Solved: conditional formatting

[vzachin]

hi,

i'm running into an issue with the following code
[vba]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[/vba]

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]

[vba]

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
[/vba]

[vzachin]

hi bob,

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

thanks again
zach

[Bob Phillips]

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]

thanks for the explanation

zach