Repeating Values

[Guest]

I thought that the following code will help me identify repeating values and
turn the font in that cell white. I found something that shows how to hide
repeating values through conditional formatting -- however, that is not
helpful to me as there is the possibility that alternate rows may be shaded
gray and it doesn't seem easy to change the font when conditional formatting
is in play.

Anyway, here is the code that I have. The if statement comes out as an
error. Can someone please help me correct my mistake? Thank you.

Sub mcrRepeatValuesFont()
'2=White; 15=Gray
'If value repeats change font of lower cell to white
Dim DataRng As Range
Dim CellRng As Range

Set DataRng = Range("k1:k1000")
For Each CellRng In DataRng
If CellRng.Offset(-1, 0).Value = CellRng.Value Then
CellRng.Font.ColorIndex = 2
End If
Next
End Sub

 

[Trevor Shuttleworth]

Elaine

the first time you go through the loop you will try to offset 1 row back
from row 1. And you can't do that. Try changing your range to be k2 to
k1000

Regards

Trevor

 

[Guest]

Thank you, Trevor. It works now.

Elaine

the first time you go through the loop you will try to offset 1 row back
from row 1. And you can't do that. Try changing your range to be k2 to
k1000

Regards

Trevor

 

[Trevor Shuttleworth]

You're very welcome.

Thank you, Trevor. It works now.