I need to generate a large number of backcolored cells from a numerical array in the sheet. I have tried with conditional formatting, but i have too many conditions, and have given up in it for now. The cells are numbered from 1 to n (fairly large, say order 1000) and have to be generated in the sheet in systematic way. However, all of them need to have individual non correlating background colors. For simplicity’s sake lets say i want to do this:
Sub TEST1 Dim SHEET As Object SHEET = ThisComponent.Sheets(0) Dim MATRIX(49,49) As Integer dim h As Integer dim i As Integer For h = 0 to 49 For i = 0 to 49 MATRIX(i,h) = i + h*50 next i next h Dim CELL As Object For h = 0 to 49 For i = 0 to 49 CELL = SHEET.GetCellByPosition(h,i) CELL.Value = MATRIX(i,h) CELL.CellBackColor = RGB(i, h, 0) next i next h End Sub
The RGB(i,h,0) is just an illustration of the fact that each cell needs to have its background color individually assigned. In my case the values of the colors for each cell are defined in another array.
Generating individual cells like this in a loop is too slow. There must be a way to do this faster. As far as i know, SetDataArray only sets numerical values?
Thanks in advance