Excel - A macro to highlight duplicate values

October 2016

Microsoft Excel is used extensively around the world for creating, handling, manipulating or comparing multiple sources of data. Excel, along with VBA, can be used to do many interesting jobs such as comparing the same value in one column to another. One can create a custom code of VBA which would look up the values in a given range and highlight cells where the value from one column matches the other. To add to the flexibility, the user can also specify the color coding where they want the matching cells to be highlighted.


I have Occupied Room numbers in column B in a sheet named Room List. Also, I have total Room numbers in column D in the same sheet. I want to highlight all numbers of column B in column D.
For example, I have numbers: 2 in cell B2, 8 in cell B3 & 6 in cell B4 in column B and numbers 1 in cell D2, 2 in cell D3, 3 in cell D4, 4 in cell D5, 5 in cell D6, 6 in cell D7, 7 in cell D8, 8 in cell D9, 9 in cell D10 in column D of the same sheet named Room List. I want to highlight by color the cells in column D containing the same numbers as column B. Here, I want to highlight cell D3 (Because it contains number 2 which is also in cell B2), cell D9 (Because it contains the same number as cell B3), cell D7 (Because it contains the same number as cell B4).
So I just want to change the color of cells D3, D9 & D7 of column D.
In short, I want to find out the same numbers written in column B from column D and highlight those cells in column D by color.
In column D, the numbers should be in their original place only, but the cells should be highlighted by different colors.


Run this macro

Confirm whether you get what you want

Sub test()   
Dim rng As Range, c As Range, cfind As Range, rng1 As Range   
Worksheets("room list").Activate   
Set rng = Range(Range("D2"), Range("D2").End(xlDown))   
Set rng1 = Range(Range("B2"), Range("B2").End(xlDown))   

For Each c In rng   
Set cfind = rng1.Cells.Find(what:=c.Value, lookat:=xlWhole)   
If Not cfind Is Nothing Then c.Interior.ColorIndex = 3   
Next c   
End Sub


Thanks to [ venkat1926 for this tip on the forum.

Related :

This document entitled « Excel - A macro to highlight duplicate values » 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.