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).