HIGHLIGHT SAME VALUE OF COLUMN B IN COLUMN D [Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
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.....

4 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
790
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
6
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 3393 users have said thank you to us this month

hi
this is a great macro ..saved a lot of my time
Very good script. Works just fine. Thanks a lot.

For those who can't use it...try changing these:

"room list" <name of you work sheet>
"B2" <column&line FROM which you want to highlight (until the end) >
"D2" <collumn&line IN which you want to highlight the values from B2 (until the end) >

whith the ones in your workbook.
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
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
790
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.
I was looking for a similar solution and hit upon your macro. I ran it in my spreadsheet and it worked very well. Thanks a lot!

Dan
Posts
2
Registration date
Thursday September 24, 2009
Status
Member
Last seen
September 25, 2009

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.
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
790
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