VBA Conditional Formatting Query

[SimonGeoghegan]

Hi All,

I currently have the following code which provides a R/A/G rating dependent on a cell value. This works as expected however I'd like to amend this so that if the cell value is 0, and the previous value is also 0, then it shows as Green, rather than Amber.

The cell value in question is between F5 and F17, and these values are based on a calculation of E5-D5 (and E6-D6 etc.). Therefore if F5 is 0 and E5 is also 0, then I'd want this to show green, currently it only shows Amber because it based purely upon the Cell Value of F5.

      'Add Conditional Formatting

    Set rg = Range("F5:F17")
       'Define the rule for each conditional format
        Set cond1 = rg.FormatConditions.Add(xlCellValue, xlGreater, "=$a$1")
        Set cond2 = rg.FormatConditions.Add(xlCellValue, xlLess, "=$a$1")
        Set cond3 = rg.FormatConditions.Add(xlCellValue, xlEqual, "=$a$1")
        'Define the format applied for each conditional format
            With cond1
            .Interior.Color = vbGreen
            .Font.Color = vbGreen
            End With
            With cond2
            .Interior.Color = vbRed
            .Font.Color = vbRed
            End With
            With cond3
            .Interior.Color = vbYellow
            .Font.Color = vbYellow
            End With

I think its condition 3 that I will need to amend.

I've tried to record a macro to do this but it was rather messy!

Any help greatly appreciated!

Many Thanks,
Simo

 

[SimonGeoghegan]

Hi All,

Managed to correct this myself now with the following code, I also added a line for removing any current formatting.

  'Add Conditional Formatting
    
    Sheets("Main Page").Cells.FormatConditions.Delete
    Set rg = Range("F5:F17")
       'Define the rule for each conditional format
        Set cond1 = rg.FormatConditions.Add(xlExpression, Formula1:="=OR(F5>0,E5=0,D5=0)")
        Set cond2 = rg.FormatConditions.Add(xlCellValue, xlLess, "=$a$1")
        Set cond3 = rg.FormatConditions.Add(xlCellValue, xlEqual, "=$a$1")
        'Define the format applied for each conditional format
            With cond1
            .Interior.Color = vbGreen
            .Font.Color = vbGreen
            End With
            With cond2
            .Interior.Color = vbRed
            .Font.Color = vbRed
            End With
            With cond3
            .Interior.Color = vbYellow
            .Font.Color = vbYellow
            End With

Regards,
Simon