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

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" <(E-Mail Removed)> wrote in message
news:0C2DF2F7-47B9-440D-8F70-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
> news:0C2DF2F7-47B9-440D-8F70-(E-Mail Removed)...
> > 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
>
>
>