Hello,
I have a spreadsheet with a macro I wrote in VBA to color duplicate
values. It works well but I have been trying to re-write the macro so that
the colors are alternating. Whereas it colors the first set of duplicates 1
color and then the second set of dupes a different color. Back and forth all
the way up the spreadsheet from the bottom of the UsedRange. Is there a way
to do this?

"Josh" wrote:

> Hello,
> I have a spreadsheet with a macro I wrote in VBA to color duplicate
> values. It works well but I have been trying to re-write the macro so that
> the colors are alternating. Whereas it colors the first set of duplicates 1
> color and then the second set of dupes a different color. Back and forth all
> the way up the spreadsheet from the bottom of the UsedRange. Is there a way
> to do this?

I also forgot to put that I am coloring the EntireRow.

Thanks!
Josh

looks like you forgot to post the code you are using also. <g>

"Josh" wrote:

>
>
> "Josh" wrote:
>
> > Hello,
> > I have a spreadsheet with a macro I wrote in VBA to color duplicate
> > values. It works well but I have been trying to re-write the macro so that
> > the colors are alternating. Whereas it colors the first set of duplicates 1
> > color and then the second set of dupes a different color. Back and forth all
> > the way up the spreadsheet from the bottom of the UsedRange. Is there a way
> > to do this?
>
> I also forgot to put that I am coloring the EntireRow.
>
> Thanks!
> Josh

>> I have a spreadsheet with a macro I wrote in VBA to color duplicate
>> values. It works well but I have been trying to re-write the macro so
>> that
>> the colors are alternating. Whereas it colors the first set of duplicates
>> 1
>> color and then the second set of dupes a different color. Back and forth
>> all
>> the way up the spreadsheet from the bottom of the UsedRange. Is there a
>> way
>> to do this?
>
> I also forgot to put that I am coloring the EntireRow.

It sounds like you already have created the row coloring code, so I'll
assume that is in place. Below is a structure you can use to alternate
colors. Note that I have used a CommandButton Click event to house the code
structure, but you would place the code structure in whatever function or
procedure is appropriate for you macro.

Enum Colors
FirstColor = 15366924 ' RGB(012,123,234)
SecondColor = 818154 ' RGB(234,123,012)
End Enum

Private Sub CommandButton1_Click()
Static Color As Long
Const ColorSum As Long = Colors.FirstColor + Colors.SecondColor
If Color = 0 Then Color = Colors.FirstColor
Color = ColorSum - Color
'
' Your code that uses alternating colors goes here
'
End Sub

Here I have used to arbitrary colors to alternate between.... substitute
your own colors for the two I used (they only have to be set in the Enum).
You could, if you want, eliminate the Enum and place your color values
directly inside the procedure, but I think the code is more self-documenting
with the Enum. Anyway, each time the procedure containing this code
structure is executed, the static Color 'variable will contain the opposite
color from the one used the last time the procedure was executed.

Rick

Thanks Rick. Here is my code for coloring the dupes. I have just recently
been teaching myself VBA so I'm sure there would have been an easier way to
color the duplicates that is universal and not based on a specific column
within the code. Please take a look and see if there is something that can be
simplified and how your code would work within. Thanks.

Sub ColorAlternates()
Dim MyRange As String
Dim F As Boolean
Dim MyCount As Integer
Dim MyCount2 As Integer
Dim MyCount3 As Integer
MyCount = ActiveSheet.UsedRange.Rows.Count
MyCount2 = ActiveSheet.UsedRange.Rows.Count - 1
MyCount3 = ActiveSheet.UsedRange.Rows.Count
MsgBox (MyCount & " " & MyCount2)
Dim i As Integer
For i = MyCount To 3 Step -1
If Cells(MyCount, "B").Value = Cells(MyCount2, "B").Value Then
Cells(MyCount, "B").EntireRow.Interior.ColorIndex = 10
Cells(MyCount2, "B").EntireRow.Interior.ColorIndex = 10
End If
MyCount = MyCount - 1
MyCount2 = MyCount2 - 1
Next i
For i = MyCount3 To 3 Step -1
If Cells(MyCount3, "B").Value = Cells(MyCount3 - 1, "B").Value And
Cells(MyCount3, "B").EntireRow.Interior.ColorIndex = Cells(MyCount3 - 1,
"B").EntireRow.Interior.ColorIndex And F = True Then
Cells(MyCount, "B").EntireRow.Interior.ColorIndex = 15
Cells(MyCount, "B").EntireRow.Interior.ColorIndex = 15
F = False
GoTo EndLine
ElseIf Cells(MyCount3, "B").Value = Cells(MyCount3 - 1, "B").Value
And Cells(MyCount3, "B").EntireRow.Interior.ColorIndex = Cells(MyCount3 - 1,
"B").EntireRow.Interior.ColorIndex And F = False Then
Cells(MyCount3, "B").EntireRow.Interior.ColorIndex = 10
Cells(MyCount3, "B").EntireRow.Interior.ColorIndex = 10
F = True
End If
EndLine:
Next i
End Sub



"Rick Rothstein (MVP - VB)" wrote:

> >> I have a spreadsheet with a macro I wrote in VBA to color duplicate
> >> values. It works well but I have been trying to re-write the macro so
> >> that
> >> the colors are alternating. Whereas it colors the first set of duplicates
> >> 1
> >> color and then the second set of dupes a different color. Back and forth
> >> all
> >> the way up the spreadsheet from the bottom of the UsedRange. Is there a
> >> way
> >> to do this?
> >
> > I also forgot to put that I am coloring the EntireRow.
>
> It sounds like you already have created the row coloring code, so I'll
> assume that is in place. Below is a structure you can use to alternate
> colors. Note that I have used a CommandButton Click event to house the code
> structure, but you would place the code structure in whatever function or
> procedure is appropriate for you macro.
>
> Enum Colors
> FirstColor = 15366924 ' RGB(012,123,234)
> SecondColor = 818154 ' RGB(234,123,012)
> End Enum
>
> Private Sub CommandButton1_Click()
> Static Color As Long
> Const ColorSum As Long = Colors.FirstColor + Colors.SecondColor
> If Color = 0 Then Color = Colors.FirstColor
> Color = ColorSum - Color
> '
> ' Your code that uses alternating colors goes here
> '
> End Sub
>
> Here I have used to arbitrary colors to alternate between.... substitute
> your own colors for the two I used (they only have to be set in the Enum).
> You could, if you want, eliminate the Enum and place your color values
> directly inside the procedure, but I think the code is more self-documenting
> with the Enum. Anyway, each time the procedure containing this code
> structure is executed, the static Color 'variable will contain the opposite
> color from the one used the last time the procedure was executed.
>
> Rick
>
>

> Thanks Rick. Here is my code for coloring the dupes. I have just recently
> been teaching myself VBA so I'm sure there would have been an easier way
> to
> color the duplicates that is universal and not based on a specific column
> within the code. Please take a look and see if there is something that can
> be
> simplified and how your code would work within. Thanks.

Here is the concept behind my code (modified to account for how you are
selecting your colors and embedded directly into the subroutine) used to
alternately color rows 3 through 100 (your post said to start at 3; I chose
100 as a limit, but you can specify whatever row value you want in the For
loop's "To" value)...

Sub ColorAlternates()
Dim X As Long
Dim IndexValue As Long
' The sum of your two specified indexes - 10 and 15
Const ColorSum As Long = 25
' Specify the start color index
IndexValue = 10
For X = 3 To 100 ' Change 100 to maximum row value desired
Range("A" & X).EntireRow.Interior.ColorIndex = IndexValue
IndexValue = ColorSum - IndexValue
Next
End Sub


Rick

Hi Rick,
Coloring alternate records isn't the problem...the problem is that I want to
color duplicate rows alternately. Let me know if this makes better sense,
thanks!

"Rick Rothstein (MVP - VB)" wrote:

> > Thanks Rick. Here is my code for coloring the dupes. I have just recently
> > been teaching myself VBA so I'm sure there would have been an easier way
> > to
> > color the duplicates that is universal and not based on a specific column
> > within the code. Please take a look and see if there is something that can
> > be
> > simplified and how your code would work within. Thanks.
>
> Here is the concept behind my code (modified to account for how you are
> selecting your colors and embedded directly into the subroutine) used to
> alternately color rows 3 through 100 (your post said to start at 3; I chose
> 100 as a limit, but you can specify whatever row value you want in the For
> loop's "To" value)...
>
> Sub ColorAlternates()
> Dim X As Long
> Dim IndexValue As Long
> ' The sum of your two specified indexes - 10 and 15
> Const ColorSum As Long = 25
> ' Specify the start color index
> IndexValue = 10
> For X = 3 To 100 ' Change 100 to maximum row value desired
> Range("A" & X).EntireRow.Interior.ColorIndex = IndexValue
> IndexValue = ColorSum - IndexValue
> Next
> End Sub
>
>
> Rick
>
>