Repeating Values
 

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

Repeating Values
 

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


"Elaine" wrote in message
...
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

Repeating Values
 

Thank you, Trevor. It works now.

"Trevor Shuttleworth" wrote:

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


"Elaine" wrote in message
...
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

Repeating Values
 

You're very welcome.


"Elaine" wrote in message
...
Thank you, Trevor. It works now.

"Trevor Shuttleworth" wrote:

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


"Elaine" wrote in message
...
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