Conditional formatting multiple cells based on highest value in row

[TheFlyingHalibut]

Hi,

I'm having trouble with conditional formatting.
I have a large sample of data looking similar to the example below, lots of dates for lots of rows.

I am trying to use the below data to create a sort of progress bar, and initially did so by counting blank cells per row, and then in a separate sheet having a progress bar =(8-Blank Cells). Unfortunately this didn't provide me with enough information.

Sent	Short	Assessed	Checked	Action	Selection	Offered	Started
22/06/2015	26/06/2015	28/06/2015	15/07/2015	28/07/2015
15/06/2015	17/06/2015	22/06/2015	12/07/2015
14/06/2015	25/06/2015	01/07/2015	05/07/2015	01/08/2015	02/08/2015
08/06/2015	15/06/2015	20/06/2015

<tbody>
</tbody>

With the information above, I want to be able to represent the data similar to the image below, so it looks like progress bars against each row, however depending on how old the most recent date is, I want the progress bar to change colour.

E.g.
If the highest date within the row is less than a week old, any cell that contains data is filled blue.
If the highest date within the row is more than a week but less than two weeks old, any cell that contains data is filled yellow.
If the highest date within the row is more than two weeks old, any cell that contains data is filled orange.

I am trying to have this set up so that "raw data" is in one worksheet, and then "progress bars" are in another nice dashboard worksheet. Main reason is that I will be dumping new data into the "raw data" worksheet on the regular from some software output and do not want to have to format it to look nice each time.

Most of my formatting so far has come from nested ifs:
e.g.

<today()-7,"c",if(data!w2,"z","")))

<today()-7,"c",if(data!ab2,"z","")))))
<today()-7,"c",if(data!ab2,"z","")))
The above example has three conditions - empty, older than a week, newer than a week.
Ideally I want a fourth condition being oldr than a week but less than two.

Please help!

Thanks,</today()-7,"c",if(data!ab2,"z","")))
</today()-7,"c",if(data!ab2,"z","")))))
</today()-7,"c",if(data!w2,"z","")))

 

[sijpie]

Excel 2010
	A	B	C	D	E	F	G	H
1
2
3	Sent	Short	Assessed	Checked	Action	Selection	Offered	Started
4	22/06/2015	26/06/2015	28/06/2015	15/07/2015	28/07/2015
5	15/06/2015	17/06/2015	22/06/2015	12/07/2015
6	14/06/2015	25/06/2015	01/07/2015	05/07/2015	01/08/2015	02/08/2015
7	08/06/2015	15/06/2015	20/06/2015
8
9
10
11	Sent	Short	Assessed	Checked	Action	Selection	Offered	Started
12
13	Yellow	Yellow	Yellow	Yellow	Yellow
14	Orange	Orange	Orange	Orange
15	Blue	Blue	Blue	Blue	Blue	Blue
16	Orange	Orange	Orange
MyForm

Cell Formulas
Range	Formula
A13	=IF(A4,IF(NOW()-MAX($A4:$H4)<=7,"Blue",IF(NOW()-MAX($A4:$H4)<=14,"Yellow","Orange")),"")
B13	=IF(B4,IF(NOW()-MAX($A4:$H4)<=7,"Blue",IF(NOW()-MAX($A4:$H4)<=14,"Yellow","Orange")),"")
C13	=IF(C4,IF(NOW()-MAX($A4:$H4)<=7,"Blue",IF(NOW()-MAX($A4:$H4)<=14,"Yellow","Orange")),"")
D13	=IF(D4,IF(NOW()-MAX($A4:$H4)<=7,"Blue",IF(NOW()-MAX($A4:$H4)<=14,"Yellow","Orange")),"")
E13	=IF(E4,IF(NOW()-MAX($A4:$H4)<=7,"Blue",IF(NOW()-MAX($A4:$H4)<=14,"Yellow","Orange")),"")
F13	=IF(F4,IF(NOW()-MAX($A4:$H4)<=7,"Blue",IF(NOW()-MAX($A4:$H4)<=14,"Yellow","Orange")),"")
G13	=IF(G4,IF(NOW()-MAX($A4:$H4)<=7,"Blue",IF(NOW()-MAX($A4:$H4)<=14,"Yellow","Orange")),"")
H13	=IF(H4,IF(NOW()-MAX($A4:$H4)<=7,"Blue",IF(NOW()-MAX($A4:$H4)<=14,"Yellow","Orange")),"")

 

[TheFlyingHalibut]

Thanks sijpie!! I was really overthinking that formula. That's absolutely perfect.

 

[howardJennings]

Hi
I have a similar problem. I have a worksheet that I use for calculating premiums on a rolling monthly basis. I indicate New policies in GREEN (that's policies that have been started between 25th of the previous month and the 30th of the current month),
Cancelled policies in RED and all others in ORANGE.

I'm trying to have the 'fill color' change automatically. I know I can use conditional formatting to achieve this, but i i have over 100 rows and i'm trying to apply it to individual rows. i.e. if I enter a cancellation date, I want just THAT row to turn RED.

It looks like this formula will do the job, I just don't understand how it works.

this is a clip of my sheet:

Sorry I can't work out how to include an image

 

[sijpie]

Post the image on a photo sharing site or your Google+ and then here click the image icon and paste in the url

 

[sijpie]

Forgot to say: you should really start a new thread. Put a link in to this thread and also put a post here with a link to your new thread

 

[howardJennings]

Hi

I've started a new thread here:_
http://www.mrexcel.com/forum/excel-...ultiple-cells-based-date-row.html#post4261690

I'm new to forums, so I hope I've done this right