conditional formating and use of colorindex

[Roger]

Excel 2003, OS XP Proff

I have a ss containing multiple line items v project gates of concept,
feasibility, business case, design, build, test, deploy, close.
The gate cells have projected dates entered into them.
The default fill color is 'none'
If a business case has been approved prior to the projected date, the
cell is filled green.
If 'today's date' equals or has exceeded the 'projected date', the
cell is marked red.
All of this to date has been handled manually but is time consuming to
say the least.

How can we conditionally format the gate cells to
1. if gate date not green and projected date is > than today's date
then cell = default (no color fill)
2. if gate date is < projected date and color fill is green then cell
= green
3. if gate date = or > projected date then cell = red
4. if gate date > projected date and color fill is green then cell is
green

It seems that we have to get the colorindex details of 'green' from
the cell to work this through!
Whats a simple way to do this?
I hope it's clear...

The other thing to add would be to sort the list on Red, or to have
the projected date - today's date (say 1 week heads up) turn orange
and sort on this color.
Cheers
Roger

 

[Gord Dibben]

Colorindex does not work with CF-colored cells.

See Chip Pearson's site for alternatives.

http://www.cpearson.com/excel/CFColors.htm


Gord Dibben MS Excel MVP