VBA code to colour dependent on cell values

[surkdidat]

Is it possible to colour cells dependent on values?

On even rows (starting row 2) - Columns C:F - If "Yes" 169,208,12. If "No" 198,224,180
On odd rows (starting row 3) - Columns C:F -If "Yes" 155, 194, 230. If "No" 189, 215, 238

Finishing row 479

 

[rpaulson]

try this.

Sub Color_it()

For r = 2 To 6 Step 2
Set myRange = Range("C" & r & ":F" & r)
If Cells(r, "A") = "Yes" Then myRange.Interior.Color = RGB(169, 208, 12) Else myRange.Interior.Color = RGB(198, 224, 180)
Set myRange = Range("C" & r + 1 & ":F" & r + 1)
If Cells(r + 1, "A") = "Yes" Then myRange.Interior.Color = RGB(155, 194, 230) Else myRange.Interior.Color = RGB(189, 215, 238)
Next r

End Sub

 

[mse330]

Hi surkdidat,

Try the below code

Sub ColorCells()
Dim Rg As Range, cell As Range
Set Rg = ActiveSheet.Range("C2:F479")
For Each cell In Rg
    If cell.Row Mod 2 = 1 Then
        If cell.Value = "Yes" Then cell.Interior.Color = RGB(155, 194, 230)
        If cell.Value = "No" Then cell.Interior.Color = RGB(189, 215, 238)
    Else
        If cell.Value = "Yes" Then cell.Interior.Color = RGB(169, 208, 12)
        If cell.Value = "No" Then cell.Interior.Color = RGB(198, 224, 180)
    End If
Next
End Sub

Edit : rpaulson posted a reply while I was writing my code

 

[Fluff]

Another option

Sub ColourRows()
   Dim i As Long
   For i = 2 To Range("C" & Rows.count).End(xlUp).Row Step 2
      Range("C" & i).Resize(, 4).Interior.Color = IIf(Range("C" & i).Value = "Yes", RGB(169, 208, 12), RGB(198, 224, 180))
      Range("C" & i + 1).Resize(, 4).Interior.Color = IIf(Range("C" & i + 1).Value = "Yes", RGB(155, 194, 230), RGB(189, 215, 238))
   Next i
End Sub

 

[surkdidat]

Excellent. Thank you very much everyone for all your replies!

 

[Fluff]

Glad we could help & thanks for the feedback