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