Home Python Excel - Color cells on condition

Excel - Color cells on condition

excel20230615172837.jpg

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.

  • Python

LEAVE A REPLY

Please enter your comment!
Please enter your name!