Ask Your Question

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

asked 2019-08-14 17:36:02 +0100

jhw gravatar image

updated 2020-09-21 23:23:32 +0100

Alex Kemp gravatar image

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.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-08-14 17:47:50 +0100

Opaque gravatar image

updated 2019-08-14 17:55:23 +0100


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.

edit flag offensive delete link more


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.

jhw gravatar imagejhw ( 2019-08-14 17:51:24 +0100 )edit

Thanks for your comment.

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

Opaque gravatar imageOpaque ( 2019-08-14 17:54:19 +0100 )edit

answered 2019-08-14 18:39:43 +0100

Set Conditional Formatting,

image description


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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-08-14 17:36:02 +0100

Seen: 122 times

Last updated: Aug 14 '19