Conditional Formatting a range of cells based on the content of 1

[mattstan2012]

OK. So I want to highhlight a range of cells based on today's date. Using Conditional Formatting, I can select an entire worksheet and ask it to highlight a cell if it has today's date - see below where it's highlighted the single cell that has today's date (3rd June 2024).

How can I ask it to search for today's date and highlight (in the example below for today's date) not only cell O23, but also O23:T39 - so essentially all of today's rota? But then tomorrow, it will highlight U23:Z39 and next Sunday, I42:N58?

If this is indeed possible?

I'm thinking this can probably only be done using visual basic?

Any thoughts would be very welcome with massive thanks in advance

 

[awoohaw]

The earlier post I made was confusing. Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		2024-06-01	2024-06-01	2024-06-01	2024-06-02	2024-06-02	2024-06-02	2024-06-03	2024-06-03	2024-06-03	2024-06-04	2024-06-04	2024-06-04	2024-06-05	2024-06-05	2024-06-05
2	Target Date: (you can use Today() function, I'm using this for testing/development	22	32	38	22	24	38	32	35	24	32	29	40	30	42	26
3	2024-06-02	43	24	39	29	41	24	23	35	31	30	25	43	34	41	40
4		36	38	44	39	42	23	25	34	42	33	44	26	28	39	43
5		30	44	33	37	34	35	39	21	35	43	36	26	35	33	22
6		34	23	44	39	21	38	38	36	25	26	28	27	26	25	38
7		40	27	23	31	21	23	37	22	40	37	35	22	25	35	38
8		43	31	21	32	36	43	25	28	22	42	44	37	37	42	38
9		25	39	31	32	36	35	25	44	25	34	40	39	21	36	40
10		21	30	29	38	22	21	41	30	36	25	22	37	31	42	29
11		44	28	24	37	29	32	39	26	36	34	37	21	25	29	32
12		40	24	23	39	22	42	44	43	25	32	39	30	33	34	25
13		31	43	22	28	28	30	28	36	44	22	29	41	33	33	40
14
15
16		2024-06-01			2024-06-02			2024-06-03			2024-06-04			2024-06-05
17		22	32	38	22	24	38	32	35	24	32	29	40	30	42	26
18		43	24	39	29	41	24	23	35	31	30	25	43	34	41	40
19		36	38	44	39	42	23	25	34	42	33	44	26	28	39	43
20		30	44	33	37	34	35	39	21	35	43	36	26	35	33	22
21		34	23	44	39	21	38	38	36	25	26	28	27	26	25	38
22		40	27	23	31	21	23	37	22	40	37	35	22	25	35	38
23		43	31	21	32	36	43	25	28	22	42	44	37	37	42	38
24		25	39	31	32	36	35	25	44	25	34	40	39	21	36	40
25		21	30	29	38	22	21	41	30	36	25	22	37	31	42	29
26		44	28	24	37	29	32	39	26	36	34	37	21	25	29	32
27		40	24	23	39	22	42	44	43	25	32	39	30	33	34	25
28		31	43	22	28	28	30	28	36	44	22	29	41	33	33	40
Sheet4

Cell Formulas
Range	Formula
E1:P1,N16,K16,H16,E16	E1	=B1+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D17:D28,G17:G28,J17:J28,M17:M28,P17:P28	Expression	=($A$3=B$16)	text	NO
C17:C28,F17:F28,I17:I28,L17:L28,O17:O28	Expression	=($A$3=B$16)	text	NO
B17:B28,E17:E28,H17:H28,K17:K28,N17:N28	Expression	=($A$3=B$16)	text	NO
B2:P13	Expression	=($A$3=B$1)	text	NO