color the duplicate value by different color

[cds]

I have data something like this

sr r1 r2 r3 r4 r5
1 12 21 25 33 39
2 14 18 25 33 41
3 12 19 21 32 33
4 8 14 19 25 32

I want color the cell depending how many nos repeated

for ex: 12, 14,19,21,32 repeated twice , so I want color them red
25,33 repeated thrice so i want color them blue
8,18,39,41 are unique , so I want colon them red


Kindly guide me to write vba


thanks in advance

 

[Scott T]

Do you really want unique values and twice repeated values red?

Try, you can play with the color values to get what you want

This gives a list of the colors and the color number.
http://dmcritchie.mvps.org/excel/colors.htm

Sub cfdups()Dim lr As Long
Dim lc As Long
Dim rng As Range
Dim numdup As Long


lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
Set rng = Range(Cells(2, 1), Cells(lr, lc))




For Each cell In rng
    numdup = Application.CountIf(rng, cell)
    If numdup = 2 Then cell.Interior.ColorIndex = 3
    If numdup = 3 Then cell.Interior.ColorIndex = 34
    If numdup = 1 Then cell.Interior.ColorIndex = 7
Next cell
End Sub

 

[C with no eyes]

Rather than vba, why don't you use conditional formatting?

To apply your rules to A:Z

Create rule =COUNTIF(A:A,A1)>1 to highlight all duplicate values.

Create a higher rule (at the top of the list) =AND(COUNTIF(A$1:A1,A1)=1,COUNTIF(A:A,A1)>1) to highlight the first of the duplicates.

 

[C with no eyes]

If my above suggestion affects your headings then select the entire row and remove conditional formatting from selected cells.

Edit: Sorry, just read your post properly and realised that I haven't quite provided what you've asked for.