Programmatically add conditional formatting

[rex759]

Hello,

My code below seems to only work if the active cell is somewhere on row 2 before I run the macro. (I have a header row)
Otherwise, the cell ranges are changed. For example, if the cursor is on B4, after I run the macro, the formula;

COUNTIF($S$2:$S$500,$A2)>0 becomes COUNTIF($S$2:$S$500,$A1048576)>0

How can I avoid activating a cell on row 2 before running the macro?

Any help is appreciated

Sub AddCondFmt()
'Add formula for validation

With Worksheets("sheet1")
    .Range("$a$2:$a$500").FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTIF($S$2:$S$500,$A2)>0"
    .Range("$a$2:$a$500").FormatConditions(.Range("$a$2:$a$500").FormatConditions.Count).SetFirstPriority
End With
 
   With Range("$A$2:$A$500").FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    
   Worksheets("Sheet1").Range("$A$2:$A$500").FormatConditions(1).StopIfTrue = False

End sub

 

[Andrew Poulsom]

Try using R1C1 style referencing in your formula. Or select the top left cell before running your code. Unfortunately, in VBA a CF formula is relative to the active cell.

 

[rex759]

ok, thank you.