Conditional Formatting Not Working In Macro

[Guest]

Hi Guys,

I have the following code at the end of my macro:

Columns("F:F").FormatConditions.Delete
Columns("F:F").FormatConditions.Add Type:=xlExpression,
Formula1:="=ISERROR(F1)"
Columns("F:F").FormatConditions(1).Font.ColorIndex = 2

Columns("G:G").FormatConditions.Delete
Columns("G:G").FormatConditions.Add Type:=xlExpression,
Formula1:="=ISERROR(G1)"
Columns("G:G").FormatConditions(1).Font.ColorIndex = 2

Columns("H:H").FormatConditions.Delete
Columns("H:H").FormatConditions.Add Type:=xlExpression,
Formula1:="=ISERROR(H1)"
Columns("H:H").FormatConditions(1).Font.ColorIndex = 2

Columns("I:I").FormatConditions.Delete
Columns("I:I").FormatConditions.Add Type:=xlExpression,
Formula1:="=ISERROR(I1)"
Columns("I:I").FormatConditions(1).Font.ColorIndex = 2

However nothing happens when this runs. Everything before it does run, so
I'm unsure as to why this is not working.

Thanks!
Dave

 

[Guest]

You set the conditions, but not the format. Try adding something like this
to each

Selection.FormatConditions(1).Font.ColorIndex = 3 '< Is red
Selection.FormatConditions(1).Interior.ColorIndex = 36 '< is light
yellow

 

[Guest]

I have set the conditions already.

See: Columns("F:F").FormatConditions(1).Font.ColorIndex = 2

Dave

 

[Dave Peterson]

Applying conditional formatting from VBA can be weird.

See John Walkenbach's Oddities page:
http://j-walk.com/ss/excel/odd/odd07.htm

One way around the problem is to select the range first.

Columns("F:F").select
Columns("F:F").FormatConditions.Delete
....