HIGHLIGHT SAME VALUE OF COLUMN B IN COLUMN D

Closed
KANSARA - Sep 24, 2009 at 04:32 AM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jun 2, 2010 at 01:14 AM
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

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Sep 24, 2009 at 06:56 AM
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
hi
this is a great macro ..saved a lot of my time
0
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.
0
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
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Sep 25, 2009 at 05:40 AM
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.
1
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
0
kansara Posts 2 Registration date Thursday September 24, 2009 Status Member Last seen September 25, 2009
Sep 25, 2009 at 02:11 AM
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.
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jun 2, 2010 at 01:14 AM
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
0