Excel 97 Conditional Formatting - greater than 3 conditions

[Jody]

I have a user who has a need to use conditional formatting
on more than three conditions. This would be simple if
the condition was only looking at the value of a
particular cell. Unfortunately, what he is doing is more
complicated.

He currently has two conditions set up. The condition
compares the current cells value to another cell. If the
values are equal or the value of the current cell is
greater than the one it's compared to, he wants the cell
to be green. If it's a negative value and equal to the
other cell or less than it, it shows green.

Now... if the cell wouldn't have turned red or green based
on the criteria above, he wants to compare the value of
the cell to a different cell than he compared it to
before. Now if it is greater than or equal to the
compared cell, it would turn a lighter shade of green. If
it were negative and less than or equal to the other cell
it would be a lighter shade of red (pink).

The way he set up the red and green formatting now was
just to set up conditional formatting for the first cell
in the row and when he drags his formula to the other
columns, the conditional formatting has been copied with
the formulas, and the conditional formatting rules have
adjusted with the "drag" so that the formatting is based
on different cells.

I haven't been able to come up with a good solution to
provide him the functionality he wants with four different
conditional formatting options.

Any ideas? I'm at a loss!

Thanks,
Jody

 

[Frank Isaacs]

The absence of any formatting on the cell could be a fourth "conditional
format," but this is a dicey solution that may not be workable for you.

More likely, you'll need to use VBA to achieve this result.
--
HTH -

-Frank Isaacs
Dolphin Technology Corp.
http://vbapro.com

 

[pfsardella]

David McRitchie shows how to have more than three conditions.

http://www.mvps.org/dmcritchie/excel/event.htm#case

If you're unfamiliar with using code,

http://www.mvps.org/dmcritchie/excel/getstarted.htm

HTH
Paul

 

[Jody]

I've seen examples like this, but my problem is that I
need to be able to pass this off to a user and he needs to
be able to set up criteria (the cells he wants to compare)
for each row. Now he's been able to do this just for the
first cell and then copy the formulas & conditional
formatting by dragging.

Is there a way I can call the Excel conditional formatting
form? Maybe then I could let him enter the first two
criteria (which are really the main criteria). Then... if
I could pull up the conditional formatting screen & be
able to pull the input the enters, then I could use that
and change the conditional formatting parameters only for
the cells that remained unchanged in the first pass??

Does this sound possible?

~~Jody