Is it possible to make a cell automatically highlighted during a time interval relative to the instant T, in real time. The cell should only be highlighted when the time comes and it becomes transparent again after the time has elapsed...

I want cell B3 to be automaticaly highlited with the green color AND filled with the matching value (Task 1 to Task 12) accordingly to the interval of time compared to the moment.

Below is what I want to obtain:

enter image description here

But unfortunatly, here is what I got: B3 is still empty and trapsarent

enter image description here

Here is the Sheet2 content:

enter image description here

I made this conditional formating:

enter image description here

Here's my file: https://docs.google.com/spreadsheets/d/1WXwT3RkM_v__zx5VTFX8ww8kmTQo2Mjx08BRLfhQcRg/edit?usp=sharing

Thank you in advance for your help

1 Answer 1


In columns A and B of Sheet2 no dates have been inserted but only text with the appearance of a date. The proof is that their format is different from that of Sheet1!B1. Then you just have to swap their month and day, in accordance with that of now(). I am attaching your file edited with also the correct conditional formatting to apply, which will leave cell Sheet1!B3 white only in the absence of TASK. Sheet1!B3 contains this formula:


P.S.: in File -> Settings -> Calculation you will have to set the recalculation at each modification and every minute.

  • Thanks @Daniele, the conditional formating you made is for not empty cell but in fact I wanted to highlight different cell ranges in Sheet1 "Form 1 for 1 cell B3 for example, Form 2 for 2 cells B3 and C3, Form 3 for 3 cells B3, C3 and D3... etc) for each task... is it possible? you can see your own file
    – Bazu
    Commented Sep 15, 2022 at 11:00
  • could you do on my file some practical examples of how you would make the requests and what you would like to achieve?
    – Daniele
    Commented Sep 15, 2022 at 14:51
  • I updated your file by adding 3 columns in Sheet2 (Length, Width and Driver), the goal is obtaining when it's time between the 2 intervals ETA and ETD a conditional formatting covering a shape based on the number of cells (length) x cells (width) AND another complication: the conditional formating has to be green if Driver is John Doe and red if it's Jane Doe
    – Bazu
    Commented Sep 16, 2022 at 4:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.