Conditional Formatting Issue

[talkinggoat]

I have a table with a cell ($I$4) that has =today() and a time range 8-17 on row 5.
The conditional formatting formula that's not working is:

=AND(TODAY() = $I$4, INDIRECT(ADDRESS(5, COLUMN())) = HOUR(NOW()))

and I have selected the range of where it's supposed to be applied.
I can use INDIRECT(ADDRESS(5, COLUMN())) = HOUR(NOW())) by itself, and it will highlight every column with the value of whatever the time is, within the selected range.
I can paste =AND(TODAY() = $I$4, INDIRECT(ADDRESS(5, COLUMN())) = HOUR(NOW())) into a cell, by itself, and it will be true. If I move over one cell, into another time, it's false.

If I paste the entire formula into conditional formatting, nothing happens. What am I doing wrong?

 

[talkinggoat]

I think there must be a bug in excel, or something...
Setting it to this seems to work:

=(TODAY() = $I$4)*(INDIRECT(ADDRESS(5, COLUMN())) = HOUR(NOW()))

 

[Eric W]

Is this how your sheet is?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
4									2/12/2025
5		8	9	10	11	12	13	14	15	16	17	18	19
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:M5	Expression	=AND(TODAY()=$I$4,B5=HOUR(NOW()))	text	NO

You do not need to use INDIRECT or ADDRESS with Conditional Formatting. When you enter a CF formula over a range, Excel automatically adjusts the ranges in the formula to offset based on the relative position of the top left cell. It works the same way if you copy a formula from a cell on the worksheet to another cell - the ranges adjust.

Although you may have a point, I've seen cases where AND doesn't seem to work as expected in CF.

Edit: I checked both your formulas, and got the same results, the first doesn't work, the second does. Weird. It may be a very subtle bug in Excel.

 

[kvsrinivasamurthy]

Max Grade Spreadsheetv3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
3
4									13-02-2025
5		7	8	9	10	11	12	13	14	15	16	17
6
Sheet1

Cell Formulas
Range	Formula
I4	I4	=TODAY()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:L5	Expression	=AND($I$4=TODAY(),B5=HOUR(NOW()))	text	NO

select B5:L5. formula for CF

=AND($I$4=TODAY(),B5=HOUR(NOW()))