# Excel - Color cells on condition

April 2018

## Issue

I have an Excel Sheet which contains Sales Figure an insight of the Excel sheet is as:

Cust. id Bill Date Itempurchased
00001 1-05-2012 Shoes
00010 1-05-2012 Shirt
00011 2-05-2012 Shoes
00010 2-05-2012 Shirt
00001 2-05-2012 Shoes
00010 3-05-2012 Pant
00001 4-05-2012 Shoes
00011 4-05-2012 Pant

I want to color cell if Cust. id and Itempurchased are same. e.g row2 & row4 and Row1, row5 & row7.

## Solution

Here is the code:

```Sub ColorDuplicate()
Dim lRow, x, y, z As Integer
Dim vCheck, wCheck As String

lRow = Range("A" & Rows.Count).End(xlUp).Row
x = 1
z = 0

Do
x = x + 1
y = 1
z = z + 1
vCheck = Range("A" & x).Value & Range("C" & x).Value

Do
y = y + 1
wCheck = Range("A" & y).Value & Range("C" & y).Value
If vCheck = wCheck And x <> y Then
Range(Cells(x, 1), Cells(x, 3)).Interior.ColorIndex = 45 + z
Range(Cells(y, 1), Cells(y, 3)).Interior.ColorIndex = 45 + z
End If
Loop Until y = lRow

Loop Until x = lRow

End Sub```

Thanks to TrowaD for this tip.
Published by aakai1056. Latest update on June 13, 2012 at 11:27 AM by aakai1056.