How to conditionally format background


I would like to format the background of a cell based on the value of another cell compared with two further cells (time range).
I’m aware how to access all the cells (with the help of indirect(Address(row…, col …)).
However I have a problem to enter the condition in the dialog for the conditional formatting.
Please refer to the attached sample spreadsheet.
if value of C1 is between the value of A1 and B1- the background of C2 should be “BLUE”
if value of E1 is NOT between the value of A1 and B1- the background of E2 should be “GREEN”
Thanks in advance for any help

PS: can I accomplish this without writing a macro?


Create two cell styles: one with background blue and other with background green. My example uses the Bad style.

Create a condition with formula: AND(A2<=C1;C1<=B2)

image description

Tested with LibreOffice (x86); OS: Windows 6.1.

Add Answer is reserved for solutions. If you think the answer is not satisfactory, add a comment below, or click edit (below your question) to add more information. Thanks.

Check the mark (Answer markCorrect answer mark) to the left of the answer that solves your question.

after a little bit of tweaking (for final layout of my spreadsheet) this answers provided the solution.
Cool dude.