Using conditional formatting to chance a cell's color to match the cell above.

[pderren]

Hi,

I wonder if you could help me work something out -

I have a minute by minute schedule document built in Excel. There are a few activities which could take place, and I'm using conditional formatting to change cell colors based on the name of the activity.

What I haven't yet worked out is this:
When a cell is empty, that is because there is no change in activity - so I would like that cell to be the same colour as the last change of activity. I think the simplest way to do that would be for any empty cell to take the color of the cell above. That would mean that if an activity was entered in a cell, the color would change to the appropriate one, and if no activity was entered the color would be that of the one above, all the way back up to the last change.

I hope that makes some sort of sense - if anyone could point me in the right direction I would be very grateful.

Thanks in advance,
P

 

[Snakehips]

P,

Welcome to MrExcel.

Below is a very simplistic example of how you might achieve what you are wanting with custom conditional formatting.

Excel Workbook
	A	B	C	D
1	Header	Header	Header	Header
2	R		G
3	G
4		R
5			R
6	R	G
7
8
9
10	G
11
12
13	R
14
W_Log

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =OR(A2="G",LOOKUP("ZZZZZZ",A$2:A2)="G")	Abc
A2	2. / Formula is =OR(A2="R",LOOKUP("ZZZZZZ",A$2:A2)="R")	Abc

I hope that helps, either directly or indirectly.

 

[pderren]

Thanks Snakehips - that's great.

Bit of fiddling to make it fit my worksheet and all sorted.

I don't think I would have got there on my own, thanks again.

P

 

[pderren]

Hi Snakehips,

Here's a follow up question then - if I haven't taken enough of your time already..

Is there a way to use Conditional Formatting to make a cell adopt the same format as an adjacent cell (which will have had it's format dictated by Conditional Formatting rules)?

i.e. I have the sheet working as intended with your help, but now I'd also like to color all of the cells in the next column to be the same - though these cells will have free text in which will be different.

So if A3 contains the word LIVE, it will have gone red, and B3 should then do the same regardless of it's contents.

Ideas gratefully received.
P

 

[Snakehips]

P,

Is this what you mean?
Continuing with the initial example the following CF rules are applied to the range A2:D?? and colour each row of columns A:D according to each 'activity change'....

Excel Workbook
	A	B	C	D
1	Header	Header	Header	Header
2	R	Any text		Any text
3	G	Any text	Any text	Any text
4		Any text		Any text
5		Any text	Any text
6	R	Any text	Any text	Any text
7		Any text	Any text	Any text
8		Any text
9		Any text	Any text	Any text
10	G
11		Any text	Any text	Any text
12			Any text	Any text
13	R	Any text	Any text	Any text
14
W_Log

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =OR($A2="G",LOOKUP("ZZZZZZ",$A$2:$A2)="G")	Abc
A2	2. / Formula is =OR($A2="R",LOOKUP("ZZZZZZ",$A$2:$A2)="R")	Abc

 

[pderren]

Facepalm. Of course. I was missing some $..!

Thanks again Snakehips.

J

 

[Snakehips]

You are welcome.