Colour range based on cell contents (1 per day of week)

[tezza]

Hi I'm trying to figure out the best was to colour in ranges of cells based on the contents of another cell, doing it once isn't a problem but doing it multiple times without creating many conditional formats is, see what you think?

Sorry, the sheet didn't recognise merged or bordered cells to show it easier.

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	W/C	Monday					Tuesday					Wednesday
4	Name	Start	unpaid break	End	Hours	Key	Start	unpaid break	End	Hours	Key	Start	unpaid break	End	Hours	Key
5	Adam	07:30		11:00	03:30	Ab				00:00		08:00		12:00	04:00	cash
6	Bryan				00:00	Hol				00:00					00:00
7	Charlie	11:00	00:30	20:00	08:30					00:00	Hol				00:00
8	Denis	14:00		18:00	04:00					00:00					00:00	d/o
9
10
11	KEY Priority		Rules
12	d/o		if key (F5) is d/o then colour b5:d5 yellow
13	Ab		if key (F5) is Ab then colour b5:d5 red
14	Hol		if key (F5) is cash then colour b5:d5 green
15	cash		if key (F5) is Hol then colour b5:d5 blue
16
17	This example applies to cells B5:D5 bases on the contents of F5
18
19	Question
20	How do I change the colours per day depending on what is in the Key without having to create dozens of conditional formats?
Sheet1

 

[Jeffrey Mahoney]

Tezza,

You will need to create conditional formatting using a formula. I will tell you what formulas to use. Here are the Excel rules governing conditional formatting based on a cell that isn't getting formatted. 1) The Top Left Most cell in the Range you want to effect is the address of the assumed target in the formula. 2) You must anchor your reference cell ($) so that each cell in the range you want to effect doesn't reference a floating location. In your case, you want 3 columns of cells in each row to reference column F, so you need to anchor column F. If you wanted to reference a single cell, you would need to anchor both the Row and Column in the formula. 3) The resulting formula needs to result in a TRUE or FALSE. You can't have formulas that result in anything else.

With that information...

Highlight the Range B5:D100 (or whatever row is last in the set). Choose Conditional formatting, New, Use a Formula to determine which cells to format. Type this formula in the formula bar:
=$F5="AB"
Then choose Red Fill as your format. Notice I anchored the F Column so that Columns B-D will look at that column only. I left the 5 without an anchor so that it would float down the rows. The formula is assuming B5 is your target because it is the TOP left cell of your selected range. Excel automatically applies the formula to all the rest of the cells using your anchor logic.

You would do the same thing for the other 3 conditional formatting. With your range still selected, enter this formula in for the next one:
=$F5="D/O"
... and then choose Yellow as your fill for the format.

Do the same for the other two.

Jeff

 

[FDibbins]

Jeff, very well explained!!

 

[tezza]

Tezza,

You will need to create conditional formatting using a formula. I will tell you what formulas to use. Here are the Excel rules governing conditional formatting based on a cell that isn't getting formatted. 1) The Top Left Most cell in the Range you want to effect is the address of the assumed target in the formula. 2) You must anchor your reference cell ($) so that each cell in the range you want to effect doesn't reference a floating location. In your case, you want 3 columns of cells in each row to reference column F, so you need to anchor column F. If you wanted to reference a single cell, you would need to anchor both the Row and Column in the formula. 3) The resulting formula needs to result in a TRUE or FALSE. You can't have formulas that result in anything else.

With that information...

Highlight the Range B5:D100 (or whatever row is last in the set). Choose Conditional formatting, New, Use a Formula to determine which cells to format. Type this formula in the formula bar:
=$F5="AB"
Then choose Red Fill as your format. Notice I anchored the F Column so that Columns B-D will look at that column only. I left the 5 without an anchor so that it would float down the rows. The formula is assuming B5 is your target because it is the TOP left cell of your selected range. Excel automatically applies the formula to all the rest of the cells using your anchor logic.

You would do the same thing for the other 3 conditional formatting. With your range still selected, enter this formula in for the next one:
=$F5="D/O"
... and then choose Yellow as your fill for the format.

Do the same for the other two.

Jeff

Thank you for replying, so does this mean I have to do each choice 7 times for the 7 days, which would be 28 conditional formats to cover the 4 options for the week?

I tried extending the range but it does the whole row but I'm trying to break it down into blocks.

The only way I 'may' be able to do it is triplicating the key box and hide 2 of the columns but it's my limitations that usually makes a meal of things

 

[Jeffrey Mahoney]

Thank you sir. I wish I had known that years ago

 

[Jeffrey Mahoney]

If your reference column changes for each day, then yes, you will have to create different conditional formatting for each.

You can copy the conditionally formatted cells over to the other days and then edit each formula so they reference the new reference column.

That would be to copy B5:B100 to G5:G100, then without changing your selection, manage the conditional formatting and change the formula from $F to $K.

Then do something similar to the other days. It will go faster.

Jeff

 

[tezza]

If your reference column changes for each day, then yes, you will have to create different conditional formatting for each.

You can copy the conditionally formatted cells over to the other days and then edit each formula so they reference the new reference column.

That would be to copy B5:B100 to G5:G100, then without changing your selection, manage the conditional formatting and change the formula from $F to $K.

Then do something similar to the other days. It will go faster.

Jeff

ok thank you, I was really hoping I'd missed an easy way to do it