Conditional formatting based on a changing value

[Sparky]

Hi

I have a row of blank cells (C1:C18). Above these are numbers 1 to 18.
In cell C19 I have a value that varies between 1 up to 18.

How do I conditionally format cells C1:C18 which are blank to display a colour if the value in C19 was for example 7?
I would then require C1:C7 to be highlighted in a colour and so on when the value changes again.

 

[Norie]

Select C1:C18, goto Format>Conditional formatting..., select Use a formula to determine which cells to format, enter this formula,

=ROW(C1)<=C$19

and set the formatting as required.

 

[Sparky]

Norie

Not working for me. Only the first cell is conditionally formatted.

 

[Norie]

What's the exact formula you entered?

 

[Sparky]

Norie

I have just had a look at what has been initially posted. It should read row C2:T2. The numbers above these are 1 through to 18 in cells C1:T1.
In cell U2 there is a changing value based on a sum that will never go above 18 but will vary from zero to 18.

 

[Sparky]

The formula entered is as you posted.

Thanks for your time.

 

[Norie]

Select C2:U2, goto Forma>Conditional formatting..., Use formula to determine..., enter this formula,

=COLUMN(C2)-2<=$U2

and format as required.

 

[Sparky]

Norie

Perfect.

Thank you