Wierd conditional formatting result (oops pressed send to soon!)

[Paul Lautman]

The Sub below is intended to extend the "Range1" to cover any new items in
column A and to enter conditional formatting to highlight any duplicates.

It works fine UNLESS the instruction "thisrange.Select" is removed. When
this is removed cell A1 ends up with the conditional format formula
"=(COUNTIF(Range1,A65527)>1)"

I'm sure that the problem lies somewhere in the expression
Formula1:="=(COUNTIF(Range1,A1)>1)"
but I am at a loss as to what I should put in its place.

Can anyone assist?

Public Sub rangered()
Set orig = Selection
Set thisrange = Range("a1",
Worksheets("Sheet1").Range("A1").End(xlDown))

thisrange.Select

thisrange.FormatConditions.Delete
thisrange.FormatConditions.Add Type:=xlExpression,
Formula1:="=(COUNTIF(Range1,A1)>1)"
thisrange.FormatConditions(1).Interior.ColorIndex = 3
ActiveWorkbook.Names.Add Name:="Range1", RefersTo:=thisrange
orig.Select
End Sub

 

[Tom Ogilvy]

relative references in a conditional format are relative to the activecell.
So you need to select Range("A1") before applying the conditional format.

 

[Paul Lautman]

Thanks for that, I knew it had to be something like that. Based on you reply
I coded:

thisrange.FormatConditions.Add Type:=xlExpression,
Formula1:="=(COUNTIF(Range1," & Selection.Address(RowAbsolute:=False,
ColumnAbsolute:=False) & ")>1)"

to cure it and save the .Select