How to use conditional formatting to highlight a cell with today's date and corresponding cells

[mattstan2012]

Hi - I have a rota spreadsheete luike this. I want to highlight today's date (i.e 14 Feb - the cell actually contains the full date but is formatted to just show "dd") and the 4 cells BELOW it in the same column. Any ideas? I've had loads of suggestions from ChatGPT, but none have worked and mainly end up highlighting the cells to the LEFT in the same ROW, not the ones BELOW in the same COLUMN

 

[Kaper]

Basically, you need to use conditional formatting based on formula.
Assuming that you have at least four (three) rows above January (I think you need 5 rows as date 00 and day of week seem to be in 2 rows. If not, adopt the solution as needed)
The formula for conditional formatting (selected cells range for formatting starts A5)

=COUNTIF(A1:A5,TODAY())>0

This is screenshot while entering the rule (don't focus on Polish names of weekdays )

And the result

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1
2
3
4
5	Jan	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26
6		śr	czw	pt	sob	niedz	pon	wt	śr	czw	pt	sob	niedz	pon	wt	śr	czw	pt	sob	niedz	pon	wt	śr	czw	pt	sob	niedz
7	Empl1	A			B	A			B
8	Empl2	B	A			B	A
9	Empl3		B	A			B	A
10
11
12	Feb	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26
13		sob	niedz	pon	wt	śr	czw	pt	sob	niedz	pon	wt	śr	czw	pt	sob	niedz	pon	wt	śr	czw	pt	sob	niedz	pon	wt	śr
14	Empl1							A			B			A			B
15	Empl2							B	A					B	A
16	Empl3								B	A					B	A
17
18
Sheet2

Cell Formulas
Range	Formula
B6:AA6,B13:AA13	B6	=TEXT(B5,"ddd")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5:AF16	Expression	=COUNTIF(A1:A5;TODAY())>0	text	NO

 

[mattstan2012]

Hi Kaper

Thanks for the suggestion, but it hasn't worked for me:

 

[Kaper]

Important part: The formula you are writing in the rule has to reflect the upper-left corner of the currently selected cells.
The second commant: I've taken into account your "the cell actually contains the full date but is formatted to just show "dd""

But seeing just a screenshot I cannot debug it. It would be much better if you use XL2BB XL2BB - Excel Range to BBCode to show the worksheet contents.