Wierd conditional formatting result.

P

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)"

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
 
T

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Wierd conditional formatting result (oops pressed send to soon!) 2
Conditional Formatting 5
Conditional Formatting in 2007 1
conditional formatting in excel vba 1
Conditional Formatting Code 4
Conditional Formatting Code 1
Programmatically setting Conditional Formatting to a range of cells 7
Conditional Formatting from VBA 10

Top