Colourcoding range between two values
 

Hi,

In the range C3:BC277 I have an arrayformula. In each row there will always
be one cell with the value 1 and sometimes a cell with the value 2. 2 will
always be to the right of 1.

The sheet is basically a gantt chart, so I would like to colour the cells
with values 1 or 2 - this is easy enough, but colouring the cells between the
1 and the 2 is giving me some trouble. I cannot use xlToRight since all cells
contains a formula, so what to do?

Any pointers?

/Sune

Colourcoding range between two values
 

Try this as the third conditional format with CF's (first CF for value 1 &
second for value 2)

=AND(SUM($C3:C3)=1,SUM(D3:$BC3) = 2)

When entering this ensure C3 is the selected cell. Copy and paste-special
formats to your range C3:BC277

Depending on what you need you might try Count instead of Sum or something
else.

Regards,
Peter T


"Sune Fibaek" wrote in message
...
Hi,

In the range C3:BC277 I have an arrayformula. In each row there will
always
be one cell with the value 1 and sometimes a cell with the value 2. 2 will
always be to the right of 1.

The sheet is basically a gantt chart, so I would like to colour the cells
with values 1 or 2 - this is easy enough, but colouring the cells between
the
1 and the 2 is giving me some trouble. I cannot use xlToRight since all
cells
contains a formula, so what to do?

Any pointers?

/Sune

Colourcoding range between two values
 

Peter,

That does exactly what I wanted it to!

I almost had a VBA solution, but that is about 50 lines of quite badly
written code and obviously must be run whenever the range changes and it has
just been scrapped.

Thanks loads,

/Sune

"Peter T" wrote:

Try this as the third conditional format with CF's (first CF for value 1 &
second for value 2)

=AND(SUM($C3:C3)=1,SUM(D3:$BC3) = 2)

When entering this ensure C3 is the selected cell. Copy and paste-special
formats to your range C3:BC277

Depending on what you need you might try Count instead of Sum or something
else.

Regards,
Peter T


"Sune Fibaek" wrote in message
...
Hi,

In the range C3:BC277 I have an arrayformula. In each row there will
always
be one cell with the value 1 and sometimes a cell with the value 2. 2 will
always be to the right of 1.

The sheet is basically a gantt chart, so I would like to colour the cells
with values 1 or 2 - this is easy enough, but colouring the cells between
the
1 and the 2 is giving me some trouble. I cannot use xlToRight since all
cells
contains a formula, so what to do?

Any pointers?

/Sune