Assigning a Coloured Cell a Number

[scottie_72]

Hi There,

I wonder if it was possible to assign a specific number to coloured cells.

I'm trying to figure out something for a holiday camp where if the room number is coloured Yellow that mean that there will be 2 people in a room and if the cell is coloured Blue that there is 1 person in a room and Green means 3 people to a room.

Then use Excel to add out how many towels are needed in total for each cleaner?

is there a way of doing this?

Any help would be appreciated.

 

[Taul]

You can use Conditional Formatting
Cell Value = 1 format as blue
Cell value = 2 format as yellow etc.

 

[scottie_72]

Hi Taul,

Sorry I should have explained a little better... on the sheet all the cells (That have the room numbers entered) have their interior coloured - I just was looking for something to total up all the Yellow/Blue & Green Cells to give me a total of what was needed.... I thought it might a simple thing when I started out!

 

[Taul]

is this what you are after?

Count The Number of Cells With Specific Cell Color By Using VBA
or this one
Sum/Count Cells By Fill Or Background Color in Excel

 

[scottie_72]

Oooh That looks interesting... Thanks Taul Greatly appreciated

 

[scottie_72]

That works great - shame it can't update if the colour changes though...

 

[Taul]

Are you changing the fill colour manually using the "Fill Color" from the ribbon?
You could add a column to type the number of people in the room and let conditional formatting change the colour for you, that may also assist in the second stage of counting the towels needed, assuming it was fixed qty of towels per person.

 

[scottie_72]

That sounds cool. At the moment I created a macro & button so that when the rooms are highlighted the button just needs to be clicked? How would you go about your idea? ( sorry for being thick!)

 

[Taul]

The idea is to form columns :-
column A = Room number
Column B = Qty of people
column C = number of towels or whatever you want.

Column B - use "conditional Format" to change the cell background colour based on the cell value.
So that if the cell value is 1, format as blue
cell value = 2, format as yellow
cell value =3, format as green

So basically, you manually type the number of people in the room and the cell background changes colour automatically.
You can add the conditional formatting to column A as well, so the room number cell also changes.

you mentioned that you have a macro assigned to a button, but what does the macro do?