Still can't use conditional formatting as you would know which to colour
green, which red, etc.
This code does it
Sub ColourThem()
Dim i As Long
Dim crows As Long
Dim dicColours
Dim iColour As Long
Dim cColour As Long
Set dicColours = CreateObject("Scripting.Dictionary")
aryColours = Array(10, 3, 6, 34, 19, 5) ' add more if needed
crows = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To crows
If WorksheetFunction.CountIf(Range("B:B"), Range("B" & i)) > 1 Then
If dicColours.exists(CStr(Cells(i, "B").Value)) Then
iColour = dicColours.Item(CStr(Cells(i, "B").Value))
Else
iColour = aryColours(cColour)
cColour = cColour + 1
dicColours.Add CStr(Cells(i, "B")), iColour
End If
Cells(i, "B").Interior.ColorIndex = iColour
End If
Next i
End Sub
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Micos3 said:
I'll try explain again
This example shows what i inteted to do:
A B
1 A3, A5
2
3 mario 11
4 joao 10
5 andre 11
I want cell A1 (ex.) give me the where the repetitions are.
Then after see ur advices i saw that this is look like impossible, so it
seems to me that the option colour is more easier to do, so what i was
pretending was, that when a value of cell was equal to other in same row,
those two would became in same colour, taking example up.
A B
3 mario 11 (gren)
4 joao 10 (red)
5 andre 11 (gren)
6 nuno 9
7 maria 10 (red)
8 ana 10 (red)
I guess now is better explained, i have all equals have same colour, i
hope r few so 3, 4 conditions will do.
