Excel - Color cells on condition

September 2017



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

Published by aakai1056. Latest update on June 13, 2012 at 11:27 AM by aakai1056.
This document, titled "Excel - Color cells on condition," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).