Conditional format on a range seems to affect only the first cell

I’m trying to create a register of pupils where junior pupils (years 1 to 6) are highlighted in yellow.
Year number is in column C. I have created a style (called Junior) which sets the background to yellow, and then a conditional formatting rule for the range A6:R6, using a formula which references C6. If the value in that cell is >=1 and <= 6 the rule should apply the style “Junior” to A6:R6.

The conditional seems to work, but only the cell A6 seems to be formatted.

A small demonstration file is attached - it has no pupil names in it.

If anyone can tell me what I’m doing wrong I would be most appreciative.


I’ve tried this with both (Debian Stretch) and (Debian Buster) with the same result.


you did not specify absolute reference to cell $C$6, but you want your cells colored reagarding to the values of exactly this single cell. Thus your condition is adapted to each column where the Conditional format is applied to. To demonstrate what I mean, just write a value 6 into cell D6 and you see the format applied to B6 and so on.

Your condition needs to be:
AND($C$6 >= 1; $C$6 <= 6)

Hope that helps.

Thank you - you are wonderful. I had tried to put the absoluteness in, but as you can see I did it in the wrong dimension. Thanks again.

Thanks for your comment.

If the answer is correct or helped you to fix your problem, please click the check mark (:heavy_check_mark:) next to the answer.

Set Conditional Formatting,

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball Descrição da imagem to the left of the answer, to finish the question.