Requires you to enable Microsoft Scripting Runtime reference in VBA Tools/References, because Sripting.Dictionary is used!
First define public variable for dictionary:
Option Explicit
Public dict As Scripting.Dictionary
Next add function:
Public Function UniqueRandomColor()
Dim Colour As Integer
Repeat:
Randomize
Do
Colour = Int(Rnd() * 56) + 1
' check if color is in dict
If dict.Exists(Colour) Then
GoTo Repeat
Else
dict.Add Colour, ""
Exit Do
End If
Loop
UniqueRandomColor = Colour
End Function
And finally test it:
Sub TEST()
Dim curColor As Variant
Set dict = New Scripting.Dictionary
curColor = UniqueRandomColor
End Sub