Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
repeating values | Excel Worksheet Functions | |||
Counting repeating cell values | Excel Worksheet Functions | |||
repeating values | Excel Worksheet Functions | |||
can i stop repeating values? | Excel Discussion (Misc queries) | |||
excluding repeating values | Excel Discussion (Misc queries) |