I am trying to set up a spreadsheet that lists the mileage of vehicles and
highlights when they are due for maintenance (between 4,500 and 5,000 miles,
between 9,500 and 10,500 miles, between 14,500 and 15,500 miles, etc.). I
have a formula that tells me the difference - this is where I would like to
have the conditional format change the color of the cell. When I put
multiple conditional formats in (i.e., between 4,500 and 5,000 miles, between
9,500 and 10,500 miles, between 14,500 and 15,500 miles, etc. as seperate
conditional formats but each with a different color), it highlights all the
numbers one of the colors (not necessarily the color that is the range).

Using CV as follows:

Cell value is Between 4500 and 5000

Set colour

Repeat for 2 other formats e.g 9500 and 10000, 14500 and 15000

Works OK for me.

"BDankas" wrote:

> I am trying to set up a spreadsheet that lists the mileage of vehicles and
> highlights when they are due for maintenance (between 4,500 and 5,000 miles,
> between 9,500 and 10,500 miles, between 14,500 and 15,500 miles, etc.). I
> have a formula that tells me the difference - this is where I would like to
> have the conditional format change the color of the cell. When I put
> multiple conditional formats in (i.e., between 4,500 and 5,000 miles, between
> 9,500 and 10,500 miles, between 14,500 and 15,500 miles, etc. as seperate
> conditional formats but each with a different color), it highlights all the
> numbers one of the colors (not necessarily the color that is the range).

On Sep 6, 12:34 pm, BDankas <BDan...@discussions.microsoft.com> wrote:
> I am trying to set up a spreadsheet that lists the mileage of vehicles and
> highlights when they are due for maintenance (between 4,500 and 5,000 miles,
> between 9,500 and 10,500 miles, between 14,500 and 15,500 miles, etc.). I
> have a formula that tells me the difference - this is where I would like to
> have the conditional format change the color of the cell. When I put
> multiple conditional formats in (i.e., between 4,500 and 5,000 miles, between
> 9,500 and 10,500 miles, between 14,500 and 15,500 miles, etc. as seperate
> conditional formats but each with a different color), it highlights all the
> numbers one of the colors (not necessarily the color that is the range).

I just used one conditional format for three conditions and cell
values between your numbers. It returned correct colors regardless
and even left the old background if the cell value was between the
ranges.

ed

Thank you - I swear I tried that and it didn't work. I think in the end I
was thinking too hard about it! You know what they say, KISS!

"Toppers" wrote:

> Using CV as follows:
>
> Cell value is Between 4500 and 5000
>
> Set colour
>
> Repeat for 2 other formats e.g 9500 and 10000, 14500 and 15000
>
> Works OK for me.
>
> "BDankas" wrote:
>
> > I am trying to set up a spreadsheet that lists the mileage of vehicles and
> > highlights when they are due for maintenance (between 4,500 and 5,000 miles,
> > between 9,500 and 10,500 miles, between 14,500 and 15,500 miles, etc.). I
> > have a formula that tells me the difference - this is where I would like to
> > have the conditional format change the color of the cell. When I put
> > multiple conditional formats in (i.e., between 4,500 and 5,000 miles, between
> > 9,500 and 10,500 miles, between 14,500 and 15,500 miles, etc. as seperate
> > conditional formats but each with a different color), it highlights all the
> > numbers one of the colors (not necessarily the color that is the range).

That happens to us all!

Anyway, glad it's sorted.

"BDankas" wrote:

> Thank you - I swear I tried that and it didn't work. I think in the end I
> was thinking too hard about it! You know what they say, KISS!
>
> "Toppers" wrote:
>
> > Using CV as follows:
> >
> > Cell value is Between 4500 and 5000
> >
> > Set colour
> >
> > Repeat for 2 other formats e.g 9500 and 10000, 14500 and 15000
> >
> > Works OK for me.
> >
> > "BDankas" wrote:
> >
> > > I am trying to set up a spreadsheet that lists the mileage of vehicles and
> > > highlights when they are due for maintenance (between 4,500 and 5,000 miles,
> > > between 9,500 and 10,500 miles, between 14,500 and 15,500 miles, etc.). I
> > > have a formula that tells me the difference - this is where I would like to
> > > have the conditional format change the color of the cell. When I put
> > > multiple conditional formats in (i.e., between 4,500 and 5,000 miles, between
> > > 9,500 and 10,500 miles, between 14,500 and 15,500 miles, etc. as seperate
> > > conditional formats but each with a different color), it highlights all the
> > > numbers one of the colors (not necessarily the color that is the range).