Ask Your Question

Format array cells for certain values [closed]

asked 2017-10-15 03:57:32 +0100

gumbypie gravatar image

updated 2020-07-21 01:25:35 +0100

Alex Kemp gravatar image

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.


edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-07-21 01:25:46.062122


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.

Lupp gravatar imageLupp ( 2017-10-15 12:39:07 +0100 )edit

4 Answers

Sort by » oldest newest most voted

answered 2017-10-15 10:17:54 +0100

Vitaly gravatar image

updated 2017-10-15 10:18:18 +0100

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

Consider this sample. C:\fakepath\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.

edit flag offensive delete link more


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

gumbypie gravatar imagegumbypie ( 2017-10-15 18:03:41 +0100 )edit

answered 2017-10-15 09:50:58 +0100

Jim K gravatar image

updated 2017-10-15 09:53:30 +0100

The following code was adapted from

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

highlighted sheet

edit flag offensive delete link more


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.

gumbypie gravatar imagegumbypie ( 2017-10-15 18:06:50 +0100 )edit

answered 2017-10-15 10:06:30 +0100

Kruno gravatar image

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.

edit flag offensive delete link more


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

gumbypie gravatar imagegumbypie ( 2017-10-15 18:04:27 +0100 )edit

answered 2017-10-15 12:30:09 +0100

Lupp gravatar image

updated 2017-10-15 13:10:50 +0100

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.)

edit flag offensive delete link more


Can't upvote yet. Many thanks.

gumbypie gravatar imagegumbypie ( 2017-10-15 18:02:58 +0100 )edit

Question Tools

1 follower


Asked: 2017-10-15 03:57:32 +0100

Seen: 431 times

Last updated: Oct 15 '17