CODEWITHSUNDEEP
excelvbacolors
cd3091
cd3091

Reputation: 87

Fixing a simple VBA code with if statements to color columns

this is my first time posting. I want to fix a code to color the columns correctly, and also to make the code simpler. Really appreciate your feedbacks.

This is for Excel VBA. Color is coded for the whole columns regardless of if statements.

No error message but the code colors the 2 whole columns red when I want them red if both the 2 corresponding cells in a row have the value.

Sub ColorCol()

Dim i As Long

For i = 2 To Rows.Count

    If Not IsEmpty(Columns("AB").Value) And Not IsEmpty(Columns("CD").Value) Then
        Cells(i, 2).Interior.Color = vbRed
        Cells(i, 3).Interior.Color = vbRed
    End If
    If Not IsEmpty(Columns("PQ").Value) And Not IsEmpty(Columns("RS").Value) Then
        Cells(i, 2).Interior.Color = vbRed
        Cells(i, 3).Interior.Color = vbRed
    End If

Next i

End Sub

Upvotes: 0

Views: 403

Answers (2)

JSRB
JSRB

Reputation: 2623

If I got your question right, this would be an approach to compare the cells in the same row and colour them red if they contain the same value:

Option Explicit

Sub ColorCol()

Dim i As Long

For i = 2 To 6

    If Cells(i, 2).Value = Cells(i, 3).Value Then
       Cells(i, 2).Interior.Color = vbRed
       Cells(i, 3).Interior.Color = vbRed
    End If

Next i

End Sub

Upvotes: 0

BigBen
BigBen

Reputation: 50162

You want to check the .Value of each individual cell.

Thus,

If Not IsEmpty(Columns("AB").Value) And Not IsEmpty(Columns("CD").Value)

becomes

If Not IsEmpty(Cells(i, "AB").Value) And Not IsEmpty(Cells(i, "CD").Value)

and similarly for 

If Not IsEmpty(Columns("PQ").Value) And Not IsEmpty(Columns("RS").Value)

You could combine the If statements (though I'm not sure it helps with readability). Use parentheses to enclose each condition:

If (Not IsEmpty(Cells(i, "AB").Value) And Not IsEmpty(Cells(i, "CD").Value)) Or _
   (Not IsEmpty(Cells(i, "PQ").Value) And Not IsEmpty(Cells(i, "RS").Value)) Then

More important would be to find the last row and loop to that instead of through all the rows. See this question for how to do that.

Upvotes: 2

Related Questions