String in a conditional formatting formula in vba

Question

I want to format column G if column D has "AI" in the same row using VBA in Excel 2007.

If i do it manually in the conditional formatting editor the formula =(D1 = "AI") works correctly.

If i try to add this formula to the Formula1 clause of a FormatConditions.Add method i must put ="(D1 = ""AI"")" or the interpreter complains. But this is copied literally with the doubled double quotes as the condition and the formatting does nothing.

What should I put in Formula1 ?

Antonio

Answer 1

This worked for me:

Sub Macro2()

Sheet1.Range("G1").Select
With Sheet1.Range("G:G")
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=F1=""AI"""
    .FormatConditions(1).Interior.ColorIndex = 3
End With
End Sub

Also, using VBA to set conditional formatting has always been a bit wonky (for me at least). The only way I could get it to work was to select G1 and then set the formatting. I know typically you don't need to select, but in this case it's the only way I could get it to work.

Answer 2

Use this formula:

=$F$1="AI"

This will result in the below.

In VBA you could do this:

="=$F$1=" + Chr (34) + "AI" + Chr (34) + ")"

Chr (34) is " (double quote)

Also you could use:

If Range("F1") = "AI" Then Range("G1").Interior.Color = 65535

Answer 3

If you are doing this in VBA, then why are you using conditional formatting at all? Simply look at the value in G and set the format of F directly in the VBA code.