Duplicating a Colour in a different sheet

Hi Guys!

I was wondering if you could help me out with a little problem i'm having with Excel.

I have two sheets. One contains two columns; hotel name and rating. The rating is a percentage and if the percantage is less than 30% the row turns red, 31 - 70% is yellow and 71%+ is green.

The second sheet has lots of columns, they include the same hotel name column as in the first sheet but not the rating column. The sheet has thousands of rows and each hotel name may be duplicated lots of times but with different data in the other columns.

I would like some way of taking the name from the hotel name column of the first sheet, seeing what colour it is and then excel automatically making any row which contains the same hotel name the same colour as it is in the first sheet.

For example: Sheet 1 =

Hilton Hotel, 90% (the row is green)
Poor Hotel, 20% (the row is red)

Sheet 2 =

Hilton Hotel, 2 nights, Breakfast included (row automatically turns green)
Hilton Hotel, 1 night, Breakfast included (row automatically turns green)
Poor Hotel, 2 nights, Room Only (row automatically turns red red)
Hilton Hotel, 7 nights, Room Only (row automatically turns green)

I hope that what I am asking is possible and that somebody can help.

Thank you for taking the time to read this post and I look forward to a solution!

Matt

Hi Leith,

Thank you for your reply and the welcome!

Unfortunately if I remove any confidential information, I would be removing everything!

There are no formulae or conditional formatting in place. I actually have two seperate workbooks, rather than two separate sheets. This is because the first workbook/sheet is constant whereas I am send a new list of hotels on a weekly basis.

Currently I am making each row red, yellow or green manually on the first sheet/workbook. I was going to try to figure out how to do that automatically myself as I can't see it being tooo difficult.

I will try to explain the fields on the spreadsheets:

First Workbook:
Hotel Name | Percentage

Second Workbook:

Hotel Name | Star Rating | Location | Twin Price | Stay From | Stay Until | Breakfast

On the first spreadsheet is simply a list of hotel names and Trip Advisor ratings.

On the second spreadsheet is a list of hotel names and the details of offers we are doing for the current week. The same hotel name can appear multiple times on the second sheet and I forget which hotels are good and which are not, so im hoping that I can automatically colour code them based on their Percentage on the first spreadsheet.

I'm sorry I can't post the workbooks. If you can't help without them then I understand, i'm just not able to upload them without breaching my companies security policies.

Thanks again,

Matt

Hi Leith,

Thank you so muh for all of your hard work. The macro is exactly what I was looking for.

I am having some difficulty in getting it to work for my workbooks though.

My "Master Book 1" is "HotelsLondon" and the sheets within this book are "3 Star", "4 Star" and "5 Star"

My "Weekly Book 2" can be changed to whatever name is required i.e. "HotelsList1".

Do I need to put the code in "HotelsList1" or in "HotelsLondon"?

I have tried both but get "Run-time error '9' Subscript out of range" even when I have changed "Master Book 1.xls" to "HotelsLondon.xls"

Also is it possible for the "Weekly Book" to automatically color code without pressing a button?

Again, thank you so much for your help!