Automatically color rows based on matching values (with separate colors for each match)

FrumpyJones

New Member
Joined
Feb 11, 2008
Messages
22
Hi Everyone,

I am getting an output that looks like this:

8/21/2017 1131312865
8/21/2017 1131312865
8/7/2017 1131221294
8/7/2017 1131221294
8/7/2017 1131221294
8/7/2017 1131221294
8/7/2017 1131221294
3/28/2017 1160019485
3/28/2017 1160019485
3/28/2017 1160019485
2/21/2017 1130300368
2/21/2017 1130300368
2/21/2017 1130300368​

This output could have 1000 rows or greater.

What I ultimately want to do is have it color the entire row based on matching numbers in column b, with a cycling color scheme (Gray/white/gray/white)

What I could live with is it just cycle color scheme the individual duplicate values.

Here's what I mean:

8/21/2017 1131312865
8/21/2017 1131312865

8/7/2017 1131221294
8/7/2017 1131221294
8/7/2017 1131221294
8/7/2017 1131221294
8/7/2017 1131221294

3/28/2017 1160019485
3/28/2017 1160019485
3/28/2017 1160019485

2/21/2017 1130300368
2/21/2017 1130300368
2/21/2017 1130300368

Is this doable?
 
Last edited:

CalcSux78

Well-known Member
Joined
Oct 15, 2013
Messages
1,120
Here's a simple loop that should do the trick:

Code:
Option Explicit
Const lColor1 As Long = 8421504   'grey
Const lColor2 As Long = 12611584  'blue

Function NextColor(ByVal lColor As Long) As Long
If lColor = lColor2 Then
    NextColor = lColor1
Else
    NextColor = lColor2
End If
End Function

Sub Highlights()
Dim rng As Range
Dim cel As Range
Dim lCol As Long
Dim v As Variant

Set rng = Range([B1], Cells(Rows.Count, 2).End(xlUp))
For Each cel In rng.Cells
    If Not cel = v Then lCol = NextColor(lCol)
    cel.EntireRow.Font.Color = lCol
    v = cel
Next cel
End Sub
 

Forum statistics

Threads
1,082,601
Messages
5,366,571
Members
400,902
Latest member
fathima

Some videos you may like

This Week's Hot Topics

Top