Loop using range contains variable

[tttommy2]

I have a sheet with a large range which is broken down to several thousand boxes of 3 cells. I want to format the boxes based on the last cell value of each box. Conditional Format is too messy and also I may need to change the way I format each box.

I have an idea with VBA but can't get it to work

Dim Box1 As Range, Box2 As Range, Box3 As Range, Box4 As Range
Dim XXX As Range
Dim i As long

Set Box1 = Range(“A1:A3”)
Set Box2 = Range(“A4:A6”)
Set Box3 = Range(“A7:A9”)
Set Box4 = Range(“A10:A12”)

For i = 1 to 4
Set XXX = "Box" & “i"
If XXX(3) > 0 Then XXX.Font.ColorIndex = 5
Next i

In the attached code I have simplified the code down to 4 Boxes from 1200 and the the formatting to setting the font color.

I am trying to get it to loop thru Range XXX = Box1, Box2, Box3, Box4, etc.

Any ideas?

Thank you

 

[Marc L]

This is the useless way as if the worksheet is enough smart designed it should be achieved directly without harcoding anything !​

​

Anyway another hardcode way without any useless Range variable :​

         Dim V
    For Each V In [{"A1:A3","A4:A6","A7:A9","A10:A12"}]
        With Range(V)
            If .Cells(3).Value2 > 0 Then .Font.ColorIndex = 5
        End With
    Next

 

[Marc L]

An easy way :​

    Dim R&
   
    For R = 3 To 12 Step 3
        If Cells(R, 1).Value2 > 0 Then Range(Cells(R - 2, 1), Cells(R, 1)).Font.ColorIndex = 5
    Next

 

[tttommy2]

Thank you Marc L. Your "easy way" is very nice and efficient way to solve my issue.

    Dim R&
   
    For R = 3 To 12 Step 3
        If Cells(R, 1).Value2 > 0 Then Range(Cells(R - 2, 1), Cells(R, 1)).Font.ColorIndex = 5
    Next