Você pode usar um dicionário para capturar o índice de cores para valores exclusivos
Option Explicit
Public Sub ColorUniquesByRows()
Const START_ROW = 2
Dim ur As Range, arr As Variant, clrIndex As Long, i As Long, j As Long, ci As Long
Dim cArr As Variant, r As Long, g As Long, b As Long, a As Double, d As Object
Set ur = Sheet1.UsedRange 'Or ThisWorkbook.Worksheets("Sheet1").UsedRange
Set d = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
arr = ur
clrIndex = 3
For i = START_ROW To UBound(arr) 'Iterate each row
For j = 1 To UBound(arr, 2) 'Iterate each column (in current row)
If Len(arr(i, j)) > 0 Then 'Ignore empty cells
If Not d.Exists(arr(i, j)) Then 'Capture color index for each unique value
If clrIndex > 56 Then clrIndex = 3 'More than 56 columns - reset indx
ci = ThisWorkbook.Colors(clrIndex) 'Determine font color vs clr index
r = ci Mod 256: g = ci \ 256 Mod 256: b = ci \ 65536 Mod 256
a = 1 - ((0.299 * r) + (0.587 * g) + (0.144 * b)) / 255
d(arr(i, j)) = clrIndex & " " & IIf(a < 0.5, vbBlack, vbWhite)
clrIndex = clrIndex + 1
End If
cArr = Split(d(arr(i, j)))
With ur.Cells(i, j)
.Interior.colorIndex = cArr(0)
.Font.Color = cArr(1)
End With
End If
Next j
clrIndex = 3 'moving to next row: reset color index and dictionary object
Set d = CreateObject("Scripting.Dictionary")
Next i
Application.ScreenUpdating = True
End Sub
Observação: isso também determina a cor da fonte com base na cor do plano de fundo
Resultado