# Thread: Code to color row if two conditions are meant

1. ## Code to color row if two conditions are meant

I don't want to wear out my welcome by asking too many questions while I learn VBA. However, after searching google for hours I decided to contact the experts who have been so kind and helpful.

The code below works well, but how can it be modified to color a row in a range when two condition are meant. The conditions are that the row have a 2 and a 3, then color it the same color.

I included a file to help.

Thanks in advance for any help provided.

Sub Cell_Color()
Dim ws As Worksheet
Dim c As Range

Set ws = ActiveSheet

'Colors cells with 2 red and cells with 3 yellow. Empty cells, includes 0, no color.
For Each c In ActiveSheet.Range("b2:k336")
If c.Value = "2" Then c.Interior.ColorIndex = 3
If c.Value = "3" Then c.Interior.ColorIndex = 6
If c.Value = "" Then c.Interior.ColorIndex = 4
Next c

End Sub

2. Excel,

This can easily be done with conditional formatting instead of vba. In you example, the conditional formula is:
=AND(COUNTIF(\$B2:\$K2,2)>=1,COUNTIF(\$B2:\$K2,3)>=1)

The range formula is:
=\$B\$2:\$K\$336

Color_Excel.png

If it can have only one of each then change the >= to just =

HTH,
Maud

3. ## The Following User Says Thank You to Maudibe For This Useful Post:

Excelnewbie (2015-04-24)

4. Maudibe-

Thanks again for your help! Thanks for teaching.

5. Excel,

To follow through with you original request using vba, this would be the equivalent:

in a standard module:
Code:
```Public Sub ColorRow()
For I = 2 To 336
If WorksheetFunction.CountIf(Range("B" & I & ":K" & I), 2) >= 1 And _
WorksheetFunction.CountIf(Range("B" & I & ":K" & I), 3) >= 1 Then
Range("B" & I & ":K" & I).Interior.Color = vbRed
End If
Next I
End Sub```

6. Maudibe-

Thanks again! As an Excel and VBA beginner this code helps me clearly see how useful a variable is.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•