CODEWITHSUNDEEP
excelvba
Geographos
Geographos

Reputation: 1456

Set color for multiple columns in VBA excel

I have some working code, based on this link:

https://software-solutions-online.com/excel-vba-column-fill-color/

and also:

Color values in Excel VBA

which looks as follows:

   Sub color()
   Sheets("10_WORLD_LIGHT_USE").Activate
   Range("CH900:CH1261").Interior.color = RGB(0, 255, 204)
   Range("CK900:CK1261").Interior.color = RGB(0, 255, 204)
   Range("CN900:CN1261").Interior.color = RGB(0, 255, 204)
   Range("CR900:CR1261").Interior.color = RGB(0, 255, 204)
   End Sub

although I am concieved, that it can be written in much shorter form. The column with the same color repeats every 3 columns.

What is the shorter way to write this command?

Upvotes: 0

Views: 3029

Answers (2)

JvdV
JvdV

Reputation: 75840

Sub color()

With Sheets("10_WORLD_LIGHT_USE")
    .Range("CH900:CH1261,CK900:CK1261,CN900:CN1261,CR900:CR1261").Interior.color = RGB(0, 255, 204)
End With

End Sub

Or:

Sub color()

With Sheets("10_WORLD_LIGHT_USE")
    Intersect(.Range("900:1261"), .Range("CH:CH,CK:CK,CN:CN,CR:CR")).Interior.color = RGB(0, 255, 204)
End With

End Sub

Upvotes: 1

SJR
SJR

Reputation: 23081

If you want a loop, you can do something along these lines:

Sub color()

Dim i As Long

With Sheets("10_WORLD_LIGHT_USE") 'don't need to activate sheet
    For i = 0 To 2
        .Range("CH900:CH1261").Offset(, 3 * i).Interior.color = RGB(0, 255, 204)
    Next i
End With

End Sub

Upvotes: 1

Related Questions