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
Dim rng As Range, c As Range, cfind As Range, rng1 As Range
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
Thanks to [ venkat1926
for this tip on the forum.
This document, titled « Excel - A macro to highlight duplicate values », is available under the Creative Commons
license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM