Color not set when Conditional Formatting applied using VBA

[forgetso]

Hi,

I have a macro that sets some conditional formatting for two columns as follows:

Option Explicit
Sub ApplyFormatting()
Dim i As Integer
Dim letter As String

For i = 16 To 17
    letter = ColumnLetter(i)
    Columns(i).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=IF(WEEKDAY(NOW(),16)=WEEKDAY(" + letter + "$2,16),1,0)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetLastPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.799981688894314
    End With
    Selection.FormatConditions(1).StopIfTrue = False
Next
End Sub

However, the .TintAndShade is never set correctly. The rule is created but under color it simply says "No Format Set".

Anyone know why this is happening?

Cheers

 

[Andrew Poulsom]

Are you sure that the condition you are adding is the first one - FormatConditions(1)? Maybe you need to delete the existing conditions first.

By the way you should use & rather than + for concatenation.

 

[forgetso]

ah ha! It is definitely not the first condition. There are about 10-20 other conditions in front of it. Thank you!

I'll remember about the "&" as well. I'm used to .'s in php and +'s in js.