Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wierd conditional formatting result (oops pressed send to soon!)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wierd conditional formatting result (oops pressed send to soon!)
relative references in a conditional format are relative to the activecell.
So you need to select Range("A1") before applying the conditional format. -- Regards, Tom Ogilvy "Paul Lautman" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wierd conditional formatting result (oops pressed send to soon!)
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 "Tom Ogilvy" wrote in message ... relative references in a conditional format are relative to the activecell. So you need to select Range("A1") before applying the conditional format. -- Regards, Tom Ogilvy "Paul Lautman" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting Against a Formula - not it's result | Excel Discussion (Misc queries) | |||
sound clip as a result of conditional formatting... ? | Excel Discussion (Misc queries) | |||
How to preserve conditional formatting on a web query table result | Excel Discussion (Misc queries) | |||
Conditional Formatting using result of Cell Formulas | Excel Discussion (Misc queries) | |||
Wierd conditional formatting result. | Excel Programming |