HIGHLIGHT SAME VALUE OF COLUMN B IN COLUMN D Closed

Question

Hello,
I have Occupied Room numbers in column B in a  sheet named  Room List. Also, I have total Room numbers in column D in 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, 8in 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 same numbers of 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 same number 8 as in cell B3), cell D7 (Because it contains same number 6 as in cell B4).
Just change color of cells D3, D9 & D7 of column D.
In short, just find out the same numbers written in column B from column D and highlight those cells in column D by colour.
In column D, the numbers should be at their original place only, but just highlight the cell by different color. 
Answers will be most helpful to me please.....

venkat1926 · Accepted Answer

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

kansara · Answer

Actually, I am not that much good in excel. So, I think I cant do this properly.
Can you guide me more please?
I will give you the perfect names of my sheet and column.
Sheet name is Room List. In that sheet, I have 4 columns.
Column A = name, column B= occupied room numbers, C= stay status (In house or vacant room), D= Total room numbers.
If I write room number in column B, which is already existing in column D somewhere,  find out that cell in column D and change the color of that column D cell, which contains same number which I wrote in column B.
I appreciate your response sir......Thank a millions.

venkat1926 · Answer

did you try the macro?
do you where to park the macro?
do you know how to run the macro 
what exactly did you get and what exactly do you want ?
then the macro can also be modifed.  

or you can post a small extract of your sheet.

venkat1926 · Answer

quote
chompgator - Jun 2, 2010 4:57am BST Hey, I got this working in Excel 2007, once, but now every time I enter it on the spreadsheet and run the Macro, it freezes my Excel, do you know why that might be? You didnt write it for Excel 2003, instead of 2007, did ya? 

Thanks for the help
unquote
I must have written the macro in excel 2002. that does not mean it should not work in ecel 2007

please post the macro you are using

HIGHLIGHT SAME VALUE OF COLUMN B IN COLUMN D

4 responses

Similar discussions

Subscribe To Our Newsletter!