Conditional Formatting Colours Using 'AND' formula.

Hi, I'm having a problem with formatting cells on an excel sheet based on a value in a merged cell on another sheet. My requirement is to colour RED for values 0-5, AMBER for values >5 and <=10 and GREEN for values >10. I have managed to get this to work (using 'Use a formula to determine which cells to format') for the RED and GREEN rules as follows:

RED: ='Operating Model Assessment 1'!$L$6:$L$11<=5

GREEN: ='Operating Model Assessment 1'!$L$6:$L$11>10

My problem is with AMBER. I'm using the following: =AND('Operating Model Assessment 1'!$L$6:$L$11>5,'Operating Model Assessment 1'!$L$6:$L$11<=10)

I'm not getting any errors but I'm also not getting the colour changing to AMBER as I am with RED and GREEN. Can anyone suggest why this is not working?

('Operating Model Assessment 1' is the name of the sheet I am referencing and '$L$6:$L$11' references the merged cell in that sheet which contains a decimal value between 0 and 15).

Hi AliGW. Thank you so much for your reply. I've switch to referencing a single cell (i.e. L6) and the formula now works so that has solved my problem very quickly. Many thanks.

The reason I initially had the range was that when I clicked in the cell to add the cell reference to the formula the full reference was added (i.e. 'Operating Model Assessment 1'!$L$6:$L$11). Nevermind though as I will remember your advice for future and ensure to change this to the single merged cell reference.

Thank you and best wishes.

Done. Thanks.