Conditional Formatting Issue

talkinggoat

New Member
Joined
Feb 1, 2022
Messages
36
Office Version
  1. 365
Platform
  1. Windows
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:
Excel Formula:
=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?
 
I think there must be a bug in excel, or something...
Setting it to this seems to work:
Excel Formula:
=(TODAY() = $I$4)*(INDIRECT(ADDRESS(5, COLUMN())) = HOUR(NOW()))
 
Upvote 0
Is this how your sheet is?

Book1
ABCDEFGHIJKLM
42/12/2025
58910111213141516171819
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B5:M5Expression=AND(TODAY()=$I$4,B5=HOUR(NOW()))textNO


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.
 
Upvote 0
Max Grade Spreadsheetv3.xlsx
ABCDEFGHIJKL
3
413-02-2025
57891011121314151617
6
Sheet1
Cell Formulas
RangeFormula
I4I4=TODAY()
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B5:L5Expression=AND($I$4=TODAY(),B5=HOUR(NOW()))textNO

select B5:L5. formula for CF
Excel Formula:
=AND($I$4=TODAY(),B5=HOUR(NOW()))
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,919
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top