CODEWITHSUNDEEP
excelvba
Andrew D
Andrew D

Reputation: 37

Set Color for next column in VBA

I am setting the color of my range by following code and it is working fine. I want to set the same color for the next column in the for each loop too. Set the same for range B5:B219

Dim companyCol As Range
   

For Each companyCol In wsLookup.Range("A5:A219")

    If companyCol.Value = "16247773" Then
        companyCol.Interior.ColorIndex = 20
        ' here I want to sent the same color for next column

        
    ElseIf companyCol.Value = "49407" Then
        companyCol.Interior.ColorIndex = 44
        ' here I want to sent the same color for next column
        
    ElseIf companyCol.Value = "16724889" Then
        companyCol.Interior.ColorIndex = 17
        ' here I want to sent the same color for next column
    
    Else '16777215
        companyCol.Interior.ColorIndex = -4142
        ' here I want to sent the same color for next column

End If
Next companyCol

referred this link but not able to solve the issue.

VBA jump to next column

any help will be appreciated.

Upvotes: 0

Views: 316

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

Try something like this:

Dim c As Range, ci As Long
   
For Each c In wsLookup.Range("A5:A219")

    Select Case c.Value 'select case is more efficient here
        Case "16247773": ci = 20
        Case "49407": ci = 44
        Case "16724889": ci = 17
        Case Else: ci = -4142
    End Select
    'color cell `c` and the next one to the right
    c.Resize(1, 2).Interior.ColorIndex = ci

Next c

Upvotes: 2

Related Questions