How to highlight entire column based on time value in Google Sheets

Question

I have a google sheet where the columns are 15 min intervals from 8am to 6pm. I'm basically making a daily gant chart by 15 min intervals. Tasks are down the left-hand side. I fill in the cells for the task based on how long they should be in. Each task gets a different color. I've set the sheet to auto-update every min.

What I'd like to do is highlight the entire column based on the current time and the time value in the column itself to show where we are in the day. Later then I'd like to fine out what cell in that entire column has colored background and highlight the task in column A as it shows that's what task you should be working on.

Any thoughts on how to do this? Would I have to make a conditional on all 32 columns? What would that conditional look like? I'm using 12 hour not 24 hour clock.

Answer 1

Assuming you are using actual time values (not Text format) in B1:AO1, please try clearing formatting from ColumnsB1:AO, selecting them and Format, Conditional formatting..., Format cells if... Custom formula is and:

=and(B$1<=1*TEXT(now(),"HH:MM am/pm"),B$1>(1*TEXT(now(),"HH:MM am/pm")-1/96))

Then select fill of choice and Done.

For the "Later" part I am confused by the colouring you want as opposed to the colouring you have.