VBA Conditional Formatting Query

SimonGeoghegan

Board Regular
Joined
Nov 5, 2013
Messages
50
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.

Code:
      '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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

SimonGeoghegan

Board Regular
Joined
Nov 5, 2013
Messages
50
Hi All,

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

Code:
  '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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,069
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top