Format array cells for certain values

[gumbypie]

I have a large rectangular array of numbers and a separate column of numbers. For each cell in the rectangular array whose value is also in the column, I would like to set a particular background colour. The rectangular array and the column can be on the same sheet or different sheets.

Seems it should be straightforward to use a macro to loop through the array and do a VLOOKUP on the column for each value. Just haven’t been able to pull that off.

Thanks.

[Lupp]

I hope you know that there is no way by standard functions to retrieve the information you calculated drudgingly first, but then only used to set a format.
Once you feel tempted to evaluate that information by a cell formula elsewhere you will need user code again (which will not exactly be reliable in case of colors for fundamental reasons).
Don’t commit the original sin.

[Vitaly]

There is another one way with conditional formatting, if you don’t need different colours for differents values.

Consider this sample. Highlighting.ods Say, the columns A-B-C are your rectangular array, and the column E is your separate column. The columns G-H-I contain formulas: value is 1 if an element is found in column E, else it’s 0. Probably G-H-I should be hidden.

Just select range from A1 to C6 and open conditional formatting dialog. You will see the formula and a user style.

[gumbypie]

Thanks. Exactly what I was trying to do. And the grid of results provides some useful metrics – an added bonus.

[jimk]

The following code was adapted from Color format Cell by duplicate character / name / value in each column / cell.

REM # This should be assigned to "Content changed" Sheet event
Sub FormatChangedCell(Optional oEvent As Object)
    Dim iRow As Long
    Dim iCol As Long
    Dim oSheet As Object
    oSheet = ThisComponent.getSheets().getByIndex(0)
    For iRow = 0 To 2  REM # rows 1 to 3
        For iCol = 0 To 2  REM # columns A to C
            FormatCell(oSheet.getCellByPosition(iCol,iRow), oSheet)
        Next iCol
    Next iRow
End Sub

Sub FormatCell(oCell As Object, oSheet As Object)
    Dim v As Variant
    Dim iRow As Long
    Const LOOKUP_COLUMN = 4  REM # column E
    Const LOOKUP_MAX_ROW = 2  REM # row 3
    iRowMax = ubound(oSheet.Data)
    v = oSheet.getCellRangeByPosition(LOOKUP_COLUMN,0,LOOKUP_COLUMN,LOOKUP_MAX_ROW).DataArray
    For iRow = 0 to LOOKUP_MAX_ROW
        If v(iRow)(0) = oCell.getString() Then
            oCell.CellBackColor = RGB(255, 255, 0)
            Exit Sub
        End If
    Next iRow
    oCell.CellBackColor = RGB(255, 255, 255)
End Sub

[gumbypie]

Many thanks. Can’t upvote yet. This is what I was trying to do. Answer from @Vitaly got me up and running but I will probably adapt this to my needs later.

[Kruno]

If that reference column is not very large, you can use conditional formating as it’s a build in feature and it does exactly what you want.

Go Format → Conditional formating → Condition.

Set this condition so value of the cell is equal to first value of reference column. Choose also a style you want to apply to result.

In a text filed at the bottom of dialog box choose rectangular array as range. Click Add and repeat condition settings for next value in reference column.

It may seam like a lot of work, but it’s easier to understand. If you have lot of values in reference column, then macro is better suited.

Maybe this can be modified to work with whole column at once.

[gumbypie]

Thanks. The reference column is too big though – thousands of values.

[Lupp]

Using “macros” for this kind of task is a bad idea. It may be encouraged by MS because they know that this helps to reinforce the incompatibility with free software. OK I sometimes am told that VBA is rather time-efficient. LibO API/BASIC may not be to the same degree.

The appropriate means for the task as I read it is Conditional Formatting (CF). See the answer and example by @Vitaly . Within reason you can also define different colors controlled by more than one condition. If needing extensive calculations based on MATCH or some variant of lookup e.g. CF will be significantly more efficient than any solution based on “macros”, but also than a solution applying the STYLE() function.

The reason is that CF conditions are evaluated (on the fly) only for cells inside the view, while cell formulas are evaluated under the rule of a recalculation queue which will at least include every cell of any sheet which is in any way - direct or indirect- referenced by any formula inside the view.

If you intend to retrieve the results of conditions via the format, CF cannot help. In this case it is anyway the only recommendable design to calculate these results in dedicated helper cells. Information explicitly needed must be explicitly accessible.

(No. I’m not a developer and I did not analyse the C++ code. I tested a lot in the field, however.)

[gumbypie]

Can’t upvote yet. Many thanks.