How to compare values of corresponding cells in multiple named column ranges and change interior color in another corresponding cell

Question

I want to write a code in Excel VBA that compares the values in corresponding cells in 3 named ranges (say, "Peter", "Paul" & "John"), and if all three values are >= 3, then the interior color of the corresponding cell in a fourth named range say, "James", is changed to Green. I wrote the code using offset but the code doesn't work properly if I insert new columns in-between "Peter", "Paul" and "John". Please can you help me write a code that uses named ranges, to avoid an error when new columns are added? Thank you.

If Cell.Value >= 3 Then
            If Cell.Offset(0, 1).Value >= 3 Then
                If Cell.Offset(0, 2).Value >= 3 Then
                    If Cell.Offset(0, 3).Value >= 3 Then
                        Cell.Offset(0, 4).Interior.ColorIndex = 4
                    End If
                End If
            End If
        End If
    Next Cell

You should probably use `AND` to make it one-line check rather than nesting so many `If` statements e.g. `If Cell.Offset(0,1).Value >=3 AND Cell.Offset(0,2).Value >=3 AND...` etc. Logically it ends up the same but it'd be neater. — jamheadart, May 31 '20 at 16:25
@jamheadart - it's not exactly the same... a one-liner means would mean all the conditions would be evaluated. — BigBen, May 31 '20 at 16:34
Yes, that's exactly what I want. I want all conditions to be evaluated for each cell. My problem is how to avoid using offset. I want to refer to the named ranges directly to ensure my code is not affected if the column number changes when I insert new columns. — mikeattah, May 31 '20 at 17:58
@BigBen does VBA not short-circuit if any of the AND conditions fail? Why not!? — jamheadart, Jun 01 '20 at 07:30

Tin Bum · Accepted Answer · 2020-05-31T18:56:15.247

This works

Dim xCel As Range, rPtr As Long
Dim xSht As Worksheet

Set xSht = ActiveSheet
rPtr = 0
For Each xCel In xSht.Range("Peter").Cells
   rPtr = rPtr + 1
   If xCel.Value >= 3 Then
      If xSht.Range("Paul").Cells(rPtr, 1).Value >= 3 Then
         If xSht.Range("John").Cells(rPtr, 1).Value >= 3 Then
            xSht.Range("James").Cells(rPtr, 1).Interior.ColorIndex = 34
         End If
      End If
   End If
Next xCel

This solves the problem you mentioned, but it still presumes all ranges are the same length and single column

How to compare values of corresponding cells in multiple named column ranges and change interior color in another corresponding cell

1 Answers1