Gfrktyl

I have lots of data, basically like this:

Name    Data    Date        ...

Groucho 123     06/23/2018

Harpo   321     05/02/2018

Chico   1239    04/17/2018

Zeppo   2938    12/03/2018

Groucho 123098  11/27/2018

Zeppo   29381   07/03/2018

...

I'd like each row with the same Name to be highlighted some color. (I don't particularly care what color if that makes it easier).

So the results would look like this:


(Note: The whole row can be highlighted, or just the row within the "table". Whatever would work is fine with me)

Is there a quick(er) way I can do this? I'd rather not have to enter a bunch of rules like =$A2="Groucho" then set a format, then new rule =$A2="Chico", and set a color, etc. as I could have dozens of names in Column A and manually creating a rule for each is rather time consuming.

I'm open to a VBA option, but prefer a built-in solution if available!

Here's a VBA Solution:

Sub conditional_format_by_name()

Dim rng As Range



On Error Resume Next

Set rng = Application.InputBox("Please select the range to Format", Type:=8)

On Error GoTo 0

If rng Is Nothing Then Exit Sub



rng.Select 'So the user can see the range selected, to know which column they want in the next step



Dim primaryCol As Long

primaryCol = InputBox("Now, **within that range**, which column number do you want to use as the basis for matches?")

rng.Columns(1).Select



Dim primaryList() As Variant

primaryList = rng.Columns(1).Value



Dim unique(), i As Long

unique = removeDuplicates(primaryList)

For i = LBound(unique) To UBound(unique)

    Debug.Print "Adding condition for: " & unique(i)

    rng.FormatConditions.Add Type:=xlExpression, Formula1:="=" & rng.Cells(1).Address(0) & "=""" & unique(i) & """"

    With rng.FormatConditions(1 + i).Interior

        .PatternColorIndex = xlAutomatic

        .Color = ColorRandomizer()

        .TintAndShade = 0.5

    End With

    rng.FormatConditions(1 + i).StopIfTrue = False

Next i

End Sub



Function removeDuplicates(ByVal myArray As Variant) As Variant

'https://stackoverflow.com/a/43102816/4650297

Dim d As Object

Dim v As Variant 'Value for function

Dim outputArray() As Variant

Dim i As Integer



Set d = CreateObject("Scripting.Dictionary")

For i = LBound(myArray) To UBound(myArray)

    d(myArray(i, 1)) = 1

Next i

i = 0

For Each v In d.Keys()

    ReDim Preserve outputArray(0 To i)

    outputArray(i) = v

    i = i + 1

Next v

removeDuplicates = outputArray

End Function

Function ColorRandomizer() As Long

'https://www.ozgrid.com/forum/forum/tip-tricks-code/102242-rgb-color-random

Dim i As Long, j As Long, k As Long, m As Long

Randomize

i = Int((255 * Rnd) + 1)

m = Int((255 * Rnd) + 1)

k = Int((255 * Rnd) + 1)

ColorRandomizer = RGB(i, m, k)

End Function

Issues: The colors for each group may be close to one another, and/or be too dark to be effective. Will think about how to work around this. May have to return R, G, and B values and check for those I've used before, and if the new values are within say 25% of an already used value, generate a new number?