Highlight dates in row not column

[ozbeachbum]

Hi All,
I am endeavouring to highlight the current date and the next 7 days, the following formula being used in Conditional Formatting [=AND($A2-TODAY()>=0, $A2-TODAY()<=7) A2 Being the first of the month] this works when the dates are listed in a column but not in a row and I have searched the net to find a solution to no avail.
Any assistance would be greatly appreciated.
Cheers,
Dave.
Column

date cf.xlsx
	A
2	Sat 01-Oct-22
3	Sun 02-Oct-22
4	Mon 03-Oct-22
5	Tue 04-Oct-22
6	Wed 05-Oct-22
7	Thu 06-Oct-22
8	Fri 07-Oct-22
9	Sat 08-Oct-22
10	Sun 09-Oct-22
11	Mon 10-Oct-22
12	Tue 11-Oct-22
13	Wed 12-Oct-22
14	Thu 13-Oct-22
15	Fri 14-Oct-22
Column

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A15	Expression	=AND($A2-TODAY()>=0, $A2-TODAY()<=7)	text	NO

Row

date cf.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	Sat 01-Oct-22	Sun 02-Oct-22	Mon 03-Oct-22	Tue 04-Oct-22	Wed 05-Oct-22	Thu 06-Oct-22	Fri 07-Oct-22	Sat 08-Oct-22	Sun 09-Oct-22	Mon 10-Oct-22	Tue 11-Oct-22	Wed 12-Oct-22	Thu 13-Oct-22	Fri 14-Oct-22	Sat 15-Oct-22	Conditional Formatting =AND($A2-TODAY()>=0, $A2-TODAY()<=7)
Row

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:O2	Expression	=AND($A2-TODAY()>=0, $A2-TODAY()<=7)	text	NO

 

[6StringJazzer]

Change $A2 to A$2 in your formula. Actually you can remove the $ completely if you are just doing the one row.

 

[ozbeachbum]

Hi Jeff,
Thank you so much, I am still on a big learning curve.
Dave.

 

[6StringJazzer]

Would it help for me to explain why you need to make that change?

 

[ozbeachbum]

Thank you that would be greatly appreciated.
Dave.

 

[6StringJazzer]

The original formula was

=AND($A2-TODAY()>=0, $A2-TODAY()<=7)

The $ in front of the A is an absolute reference. That means no matter what cell you are formatting, use the value in column A to determine whether to format it. In your case you are just formatting column A anyway. But if you extended this formula into other columns, all columns would be formatted based on the value in column A.

When you tried to use the same formula to format a row, all cells in the row were still tied to column A even though you now really want to format based on each cell. That is, cell B2 will be formatted based on A2, C2 will be formatted based on A2, and so on. So if you remove the $ each cell will be formatted based on its own value, instead of the value in column A.

This same principle applies to formulas in your worksheet. With a $, it always refers to that row or that column. Without it, it refers to a relative row or column. You can search on "Excel absolute relative referencing" for much more information.

 

[ozbeachbum]

Thank you.
You are very helpful.
Dave.