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

pderren

New Member
Joined
Oct 2, 2014
Messages
4
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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
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
ABCD
1HeaderHeaderHeaderHeader
2RG
3G
4R
5R
6RG
7
8
9
10G
11
12
13R
14
W_Log
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A21. / Formula is =OR(A2="G",LOOKUP("ZZZZZZ",A$2:A2)="G")Abc
A22. / Formula is =OR(A2="R",LOOKUP("ZZZZZZ",A$2:A2)="R")Abc


I hope that helps, either directly or indirectly.


 

pderren

New Member
Joined
Oct 2, 2014
Messages
4
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

New Member
Joined
Oct 2, 2014
Messages
4
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

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
ABCD
1HeaderHeaderHeaderHeader
2RAny textAny text
3GAny textAny textAny text
4Any textAny text
5Any textAny text
6RAny textAny textAny text
7Any textAny textAny text
8Any text
9Any textAny textAny text
10G
11Any textAny textAny text
12Any textAny text
13RAny textAny textAny text
14
W_Log
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A21. / Formula is =OR($A2="G",LOOKUP("ZZZZZZ",$A$2:$A2)="G")Abc
A22. / Formula is =OR($A2="R",LOOKUP("ZZZZZZ",$A$2:$A2)="R")Abc


 

pderren

New Member
Joined
Oct 2, 2014
Messages
4
Facepalm. Of course. I was missing some $..!

Thanks again Snakehips.

J
 

Watch MrExcel Video

Forum statistics

Threads
1,122,476
Messages
5,596,373
Members
414,063
Latest member
N_Bates

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top