PDA

View Full Version : Solved: conditional formatting



vzachin
01-27-2010, 07:26 AM
hi,

i'm running into an issue with the following code
Sub test()
With Sheets("total")
Set c = .Range("H6", .Range("H" & Rows.Count).End(xlUp))
End With
Sheets.Add.Name = "A"
c.Copy Sheets("A").Range("h6")
With Sheets("A")
.Columns("H:H").FormatConditions.Delete
Set c = .Range("H1", .Range("H" & Rows.Count).End(xlUp))
c.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($H$1:H1,H1)>1"
c.FormatConditions(1).Interior.ColorIndex = 35
End With
End Sub

when i check the Conditional Formatting in Sheets("A"), it shows as follows:
=COUNTIF($H$1:O6,O6)>1

it should be "=COUNTIF($H$1:H1,H1)>1

how can i correct this?

thanks
zach

Bob Phillips
01-27-2010, 10:25 AM
Sub test()
With Sheets("total")

Set c = .Range("H6", .Range("H" & Rows.Count).End(xlUp))
End With

Sheets.Add.Name = "A"

c.Copy Sheets("A").Range("h6")

With Sheets("A")

.Columns("H:H").FormatConditions.Delete
Set c = .Range("H6", .Range("H" & .Rows.Count).End(xlUp))
c.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($H$1:" & ActiveCell.Address(False, False) & "," & ActiveCell.Address(False, False) & ")>1"
c.FormatConditions(1).Interior.ColorIndex = 35
End With
End Sub

vzachin
01-27-2010, 02:18 PM
hi bob,

thanks for the code. if you're not too busy, can you explain ActiveCell.Address(False, False) & "," & ActiveCell.Address(False, False)

thanks again
zach

Bob Phillips
01-27-2010, 03:15 PM
Instead of entering the relative cell address H1 as you did, I enter the relative cell address of the active cell. This is because CF automatically adjusts a formula in relation to the activecell. You can do it tjhis way, or select H1 andd then use H1.

vzachin
01-27-2010, 07:30 PM
thanks for the explanation

zach