conditional format

[JOEE1979]

I have a conditional format (cell value of N4:N27 is greater than N1 then highlight cell) but it dont work, not sure why.

Weekday Bus Cycling - January 2023 - steeprock.xlsx
	N
1	19:17:02
2	Sign on
3
4	10:05:00
5	10:40:00
6	11:50:00
7	12:05:00
8	12:19:00
9	12:50:00
10	13:20:00
11	13:30:00
12	13:35:00
13	13:55:00
14	14:05:00
15	14:20:00
16	14:30:00
17	14:45:00
18	15:05:00
19	21:30:00
20
21
22
23
24	10:45:00
25
26
27
Table 1

Cell Formulas
Range	Formula
N1	N1	=NOW()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N4:N27	Cell Value	>=$N$1	text	NO

 

[JOEE1979]

how would I be able to add how many highlighted cells show up?
I tried
=CountCellsByColor(data_range, cell_color) but that didnt work

 

[Peter_SSs]

how would I be able to add how many highlighted cells show up?

You would use exactly the same condition as the conditional formatting used. Something like

=COUNTIF(N4:N40,">="&N1)

You should note though that neither the conditional formatting nor this formula will automatically update as time goes by. There needs to be something to cause the worksheet to recalculate to trigger any reassessment of the formula or conditional formatting results.

 

[JOEE1979]

You would use exactly the same condition as the conditional formatting used. Something like

=COUNTIF(N4:N40,">="&N1)

You should note though that neither the conditional formatting nor this formula will automatically update as time goes by. There needs to be something to cause the worksheet to recalculate to trigger any reassessment of the formula or conditional formatting results.

I put a VBA code in the workbook

Sub Refresh_My_Data()

ActiveWorkbook.RefreshAll

Call Update_Data

End Sub

Sub Update_Data()

Application.OnTime Now + TimeValue("00:05:00"), "Refresh_My_Data"

End Sub

Let me know if you have a better code.
Thanks

 

[JOEE1979]

the problem I'm having is that the time won't update automatically, I have to type something to make it refresh