How can you do that with conditional formatting?

[excelos]

Hello

I have a table and I want to conditionally format it.

I want to make red the cells that belong to the column of the cell of the top row that is after today's date and only if they are above zero.

Is there a way to do that? I tried to specify the top row but it does not seem to take it into account.

Thanks

 

[Peter_SSs]

Like this? Current date here is 9 October

Excel Workbook
	C	D	E	F	G	H	I
1	07-Oct-18	08-Oct-18	09-Oct-18	10-Oct-18	11-Oct-18	12-Oct-18	13-Oct-18
2	0	1	2	2	1	0	0
3	0	0	3	0	3	2	3
4			1	0	1	0	1
5			1	3			3
6	0	0	0	1			1
7	1	3	2	1	1	0	0
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2	1. / Formula is =AND(C2>0,C$1>TODAY())	Abc

 

[excelos]

Like this? Current date here is 9 October

Excel Workbook
	C	D	E	F	G	H	I
1	07-Oct-18	08-Oct-18	09-Oct-18	10-Oct-18	11-Oct-18	12-Oct-18	13-Oct-18
2	0	1	2	2	1	0	0
3	0	0	3	0	3	2	3
4			1	0	1	0	1
5			1	3			3
6	0	0	0	1			1
7	1	3	2	1	1	0	0
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2	1. / Formula is =AND(C2>0,C$1>TODAY())	Abc

Thanks that's great.
But I noticed it's a bit trickier.

Can you do it mark as red the cells that extend beyond (to the right) the first cell that is larger than today's date AND is not zero? up to the end of the rows?

 

[Peter_SSs]

Can you do it mark as red the cells that extend beyond (to the right) the first cell that is larger than today's date AND is not zero? up to the end of the rows?

I don't understand.

Can you show an example?
Or tell me which cells in my screen shot should be red, which ones should not and WHY?

 

[excelos]

I don't understand.

Can you show an example?
Or tell me which cells in my screen shot should be red, which ones should not and WHY?

Yes sure.

Well, I want to mark red a specific range in each row.
It's a bit tricky.
The end cell of that range would be the last non zero cell where the date is past today.
The start cell of that range would be the first cell where the date is past today.

So, I suppose if the end cell does not exist, it shouldn't highlight anything.

It's so tricky, any idea?

So in your example, the cells between the red ones should also be filled with red!

 

[Peter_SSs]

So in your example, the cells between the red ones should also be filled with red!

But you said (originally) to colour the cells "only if they are above zero"
Has that requirement changed?
If so, it would just mean colour all cells in all columns whose date is after today, wouldn't it?

Edit: Or do you mean just these extra cells in my post? H4, G5, H5, G6, H6

 

[excelos]

Edit: Or do you mean just these extra cells in my post? H4, G5, H5, G6, H6

Exactly, plus F3, F4

 

[Peter_SSs]

Date here is now 10 October so I've changed the row 1 dates.

Excel Workbook
	C	D	E	F	G	H	I
1	08-Oct-18	09-Oct-18	10-Oct-18	11-Oct-18	12-Oct-18	13-Oct-18	14-Oct-18
2	0	1	2	2	1	0	0
3	0	0	3	0	3	2	3
4			1	0	1	0	1
5			1	3			3
6	0	0	0	1			1
7	1	3	2	1	1	0	0
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2	1. / Formula is =AND(COUNTIF(C2:$I2,">0"),C$1>TODAY())	Abc

 

[excelos]

Date here is now 10 October so I've changed the row 1 dates.

Excel Workbook
	C	D	E	F	G	H	I
1	08-Oct-18	09-Oct-18	10-Oct-18	11-Oct-18	12-Oct-18	13-Oct-18	14-Oct-18
2	0	1	2	2	1	0	0
3	0	0	3	0	3	2	3
4			1	0	1	0	1
5			1	3			3
6	0	0	0	1			1
7	1	3	2	1	1	0	0
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2	1. / Formula is =AND(COUNTIF(C2:$I2,">0"),C$1>TODAY())	Abc

Brilliant thank you! Can you explain the logic in english?

 

[Peter_SSs]

Brilliant thank you! Can you explain the logic in english?

The formula just checks that the following 2 conditions are met. If so, the cell is formatted.
- the heading of the column is a date after TODAY()
- the cell itself, or any cell to the right of it, is greater than zero