How to change coloured cells to one colour

[MalR]

Howdy guys. We have a booking program done in Excel 2010. (Win 7, 64 bit). When people book we designate the length of stay by colouring the cells. The next lot of customers get a different colour so we can see where the changeover occurs. So we have 14 columns (cabins) containing different coloured cells. Some of these colours can be in one cell (one day booking) or in say 21 cells (three weeks). Each booking name has one colour only.
We display the worksheet on our website to show the cabin availability. Unfortunately the different colours causes confusion for some people.
I need a macro or a VLookup that starts at the top of each column, goes down, finds a coloured cell and changes it to one colour, say grey or whatever will display best in HTML. (Customers need to be able to read the text in each cell). I should say that when we put it onto the web we transpose the worksheet so the columns can be around 6 pages long. I made a short clip and attached it to give you an idea what we are after.
I did research this but the macros that change colours in cells seem to be triggered by other internal signals.
Thanks for your help again
Mal

 

[SirJB7]

Hi, MalR!

You should 1st start killing the guy who developed that f.....g worksheet (the date in bold pink!?), then firing (or moving , don't be so evil, improve Google's motto) colorblind employees or at least provide them with , guiding puppies and white sticks. (Joke, just in case... ... joke?... ...)

BTW, the 8th and 15th cabins have been stolen? :D And there's a 13th one!

Now that I can't use any more smiles, my comments and doubts:
a) Regarding your requirement it'd be a macro, VLOOKUP and MATCH and LOOKUP and any other (if I miss it now) function can't perform searches by anything than cell values, so it doesn't work on cell formats.
b) This worksheet is the output produced by your booking workbook?
c) Are you going to use cabin numbers (i.e., all equal for a given column) or a customer name or ID that changes within the column?
d) It's up to you to define the output colors, it'd be helpful if you upload all the possible colors that your booking stuff uses and the equivalence you want to set for each one in the requested macro.

Regards!

PS: f.....g stands for... ehmmm... fooling, of course.

 

[MalR]

Howdy Sir. You should be in the circus. (As the GM, not the clown of course). I won't tell you the name of the global expert who designed the sheet! The dates are like that because I have a Go To Today macro that works off them. And you realize wives like pretty colours.
I copy a section of the booking program. I have a template that I paste this into and then do a fair amount of formatting before I upload it to the web. (Delete text, remove borders, delete comments). I then put in the vertical line separators, font to Arial 14, centre text, text all black, dates in bold, row hts to 25, columns to 12.
I realize a macro could do all this but once the area is highlighted it is simple to press Excel's control icons to do it.
I can get the worksheet ready to upload in about a minute doing this but I cannot quickly change the cell colours of course. Hence the macro and hopefully you....
Your questions:
Yes, I copy a section of the booking program each day, format it and upload it. There is dialogue stating that it is accurate to a certain time. Hence customers cannot book from it. They need to phone.
I need to show the text in each column (cabin numbers). No names. Nothing changing down the column. The numbers will be the same all the way down. The top line ($1:$1) gets put in on each sheet in the web from my template. The macro should avoid the top line. Thus customers look at the column which says 2 (meaning Cabin 2) and by the colour they can see the dates that Cabin 2 is not booked.
The colours come from Excel's colour drop down. Of the 10 rows of colours in the drop down any in the second row come up nicely under HTML. Blue would be nice.
Point d) might be a problem. I assume from your comment the macro needs to find the colour codes? There is no rationale in what colours we use. We just take any from the colour palate, avoiding dark colours because they hide the text (cabin numbers). Thus it just happens that there are quite a few different colours used. I had assumed that the macro would see a colour and change it to another colour. If it needs the codes then that may make it undoable? Can you write in a colour range to capture all colours in the macro?
Basically I need the macro to avoid the top row then go down each column and where a cell is coloured it changes it to (say) the second row blue in column 4 of the Excel palate.
I attach a formatted section to show what it looks like before it is uploaded.
In the final presentation the sheet, instead of having numerous different colours will have one colour (light blue) only.the colour

As an alternative thought; If it makes the macro easier to write can it detect the three colours in row 1 then change any coloured cell below this to the colour in the top row. Meaning for instance, Open Plan Cabins (yellow in the attachment) the macro will change any colour below it in those 6 rows to the yellow that is in the top row. The same for the colours in the One Bedroom and Two bedroom columns (light and dark blue). If this is feasible I will put better colours in those rows because dark blue tends to hide the cabin numbers.
I hope this helps. Without knowing how the macro will work I am afraid I can only offer suggestions.
Regards
Mal

 

[Hui]

@SirJB7
Your post is a good example of why emoticons should be disabled

 

[Hui]

Would you be better off not having the number in each cell
Leave the Cabin Numbers ion the top row
and put the names in each cabin/date combo
see below:

 

[MalR]

Thanks for your input Hui. For simplicity and privacy we thought it is best to just display the cabin numbers. They are easier to apply to each cell than a name or abbreviation. I just highlight across the complete top row and drag down. It also means that when the customers view it on the web they can see the cabin number from any position on the sheet. People focus on two things when booking. The cabin and the date. 80% are repeat customers and they want a specific cabin and they have a specific time they can come.
Each booking has a comment box and text that allow us to supply each customer with things they request such as linen, air conditioner key, special parking etc. These all get stripped to leave just the colours and we put in the cabin numbers after the sheet has been stripped but before it is uploaded. We do not have to have any cabin numbers. We could leave the cells coloured only if that is what is required for the macro to work.
Regardless of the text in the cells (numbers or letters or nothing) the thing that would improve the display is to have those various colours displayed as one colour only. The colour designates that the cabins are booked on those days.
Although not entirely perfect, we have attempted to conjure something simple on the web to show cabin availability over time. The alternative is to purchase or lease an expensive professional program which does this. Ours seems to work quite well and takes limited time to format and upload. When customers make suggestions, we try to make changes and the one suggestion at the moment is that some get confused by the different colours and expect them to have some meaning. To alleviate this we thought a macro could change all colours to one specific colour (or hatched cell etc). Whatever is easiest. As long as it shows that those cabins are not available over those periods.
Hope this helps
Mal

 

[SirJB7]

@SirJB7
Your post is a good example of why emoticons should be disabled

@Hui
Hi!
But don't tell me they unsuitable or unfair regarding the rainbow displayed at the worksheet!
Regards!

 

[SirJB7]

c) Are you going to use cabin numbers (i.e., all equal for a given column) or a customer name or ID that changes within the column?
d) It's up to you to define the output colors, it'd be helpful if you upload all the possible colors that your booking stuff uses and the equivalence you want to set for each one in the requested macro.

Would you be better off not having the number in each cell
Leave the Cabin Numbers ion the top row and put the names in each cabin/date combo see below:

Hi, MalR!

As both Hui and me (c) suggested you're right that regarding privacy names shouldn't be used, but that leads us to sse an ID, call it guest number, customer ID, anything. Do you have any value (string or numeric) that identifies users? I assume you do. If so, would it be possible for you to print a small paper credit card sized (better if plasticized or at least in thick cardboard) with the cabin n°, the dates from/to and the user name? This will let every user to identify himself in the published page without any doubts. You can do it in Excel entering the user reference and the assigned color.

About the macro, it won't make any difference using just colors, colors and cabin numbers, or adding user ID and name.

In your uploaded workbook there're no macros, so I can't try to derive how are colors assigned. If you can post the code it'd help.

About colors and number of colors. I see that cabins 1, 3, 9 & 12 share the same color. That's because it's the same user group or because the program assigned it? Shouldn't it be a differente color for each user? If so, you'd have to think in 14*printed_days (30, 60, ...). Or a range of colors per cabin? Anyhow, it's up to you to define that.

Regards!

 

[MalR]

Sir I guess my intention is not getting through to this forum. I strip out all text and formatting from the booking sheet so that I finish with just a series of colours (the rainbow) in a table. The colours designate when any cabin is booked. I want absolutely nothing to show on the web that relates to a customer number or ID. It is meaningless to upload anything like that. All customers want to see is when any cabin is available to be booked and what dates.
Colours are randomly put into the cells in my booking program with no rationale whatsoever. There is no assignment of colours. In fact we cannot use say one colour for any cabin in our booking program because we cannot differentiate the point when someone leaves and someone else comes in on the same day if the colours are the same. You can clearly see that from the two files I uploaded here. If everything was the same colour we would have no idea when people are coming in or leaving. But when it is displayed on the web it is inconsequential. All we want to show there is when cabins are unable to be booked (or are available for booking).
There is no macro uploaded.
In preparing the file for uploading, the table is stripped of all formatting to leave only the colours. It is a blank table with colours only. I was hoping someone could give me a macro that I could use to change the colours into one simple colour. I see heaps of similar macros on the web but none that suit my purposes so I have asked this forum if they can provide one. Maybe this is too complicated to do? I do not know how to write macros so I do not know if what I ask is possible. I assume from all these questions that it is more difficult to do than I imagined.
If I could get a macro to change the colours into one colour then I will apply some Excel formatting and then upload it. Basically it goes up as a picture. It is not interactive. It is impossible for the customer to identify himself and we under no circumstances want this happening. They just look at the spaces which have no colours which means that the cabins are vacant then they ring us to book those dates. We take a deposit from them and assign them any colour from the drop down palate in our booking program. Then the whole process is updated and repeated the next day.

 

[SirJB7]

Hi, MalR!
Got the point, in a couple of days I'll be working on this macro. In the meanwhile you should define the colors you'd use, since I will set up a table with any color in any order or preference. Then you'd be able to change it.
Regards!