Excel - Color cells on condition

December 2016



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.

Related :

This document entitled « Excel - Color cells on condition » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.