Search code examples
excelvbaconditional-statementscolor-coding

How to colorcode cells based on conditions?

I am trying to get this loop to repeat down both rows from O14:P434. I want it to run for the entire range but only apply the coloration if there is a value in column P.

For loopctr = 14 To 434
Dim gooddate As String
goodate = "O14"
Dim baddate As String
baddate = "P14"
If baddate > gooddate Then
Range("P14").Select
With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With


End If

Next

The loop I have doesn't work, how do I make it run all the way down those rows. I got it to work through conditional formatting and recording the macro of creating it.

Solution

  • For equivalent data and results to @GMalc's A, the following CF formula rules work:

    Red: =C1>B1, Green: =AND(C1<>"",C1<B1)

    if applied to ColumnC. Details of how to apply CF here.