VBA Script keeps Changing my Formula when it runs!!

I created a VBA Script to run a conditional formatting on a sheet to highlight name matches.
Each time the script is ran the one part of the formula gets changed, coincidentally and maybe related, the number it changes to is one more than the color code.

Here is part of my script and the formula.

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($J$1:$J$108,$A1)>0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0

The formulac keeps changing to

"=COUNTIF($J$1:$J$108,$A65536)>0"

and highlighting the cell below the one it should be.


Any help or guidance would be greatly appreciated.

Sub MatchNames()
ActiveWindow.SmallScroll Down:=75
Range("J2:J108,A1:A1000").Select
Range("A1").Activate
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($J$1:$J$108,$A1)>0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
ActiveWindow.LargeScroll Down:=-22
ActiveWindow.SmallScroll Down:=102
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 128
ActiveWindow.ScrollRow = 127
ActiveWindow.ScrollRow = 126
ActiveWindow.ScrollRow = 125
ActiveWindow.ScrollRow = 123
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 110
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 99
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
End Sub

Originally Posted by Norie

Which range do you want to apply the conditional formatting to?

The A1:A1000 range. Thanks for the reply!!