vba help for Conditional Formatting

[rsadambrown]

Hi everyone! I'm looking at throwing in some conditional formatting to a macro I am writing. The range is dynamic and can change, and the conditional formatting is based off of another cell. I've tried recording the macro and copy+pasting that result, but the result is just making the entire selection red, instead of just the cells that meet the condition. I've tried some searching on here and in google, to no avail.

Here's a code sample. When stepping threw the macro, the first two lines select the range correctly, but when selection.style is executed, it just makes the whole range 'bad'. The intent is just to make any value in the range > the value in cell C25 on the 'Macro' tab 'bad'.

Set TestArea = Application.Range(Cell1:="G2", Cell2:="G" & EndingDown)TestArea.Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=Macro!$C$25"
Selection.Style = "Bad"

Thanks, and I appreciate any help!

 

[bobsan42]

the last row in your code just sets everything to style BAD. With this code you apply Formatting, rather than Conditional formatting:

Set TestArea = Application.Range(Cell1:="G2", Cell2:="G" & EndingDown)
TestArea.Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=Macro!$C$25"
[COLOR=#0000ff]    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority[/COLOR]
[COLOR=#0000ff]    With Selection.FormatConditions(1).Interior[/COLOR]
[COLOR=#0000ff]        .PatternColorIndex = xlAutomatic[/COLOR]
[COLOR=#0000ff]        .Color = 255[/COLOR]
[COLOR=#0000ff]  [/COLOR][COLOR=#0000ff]   End With[/COLOR]

You can adjust the formatting in the WITH block as you like it. you may even use the macro recorder to get the values you need.

 

[rsadambrown]

Thanks for the help!