Using Excel 97's Conditional Formatting Form

[Jody]

Can I call Excel's Conditional Formatting form using code
and use the results of the criteria entered in my own way?

The users want to be able to apply four conditional
formats and Excel has a maximum of three that can be
used.

This would be easy if they were just formatting the cell
based on the cell's contents alone. What they are doing,
however, is comparing the cell's contents to another cell
and using a color scheme based on how the cells compare to
each other. They've been able to set up the first cell in
a row with their formulas and conditional formatting
formulas and then copy by dragging into the other cells in
the row. When they copy, the conditional formats also
copy and adjust to point to the correct cells for
comparison. For instance, if the first cell's conditional
formatting compared A1 to A10 and they dragged to copy
into the second cell, right now the conditional formatting
is adjusting for the second cell and comparing A2 to A11.

So... I was thinking possibly I could let them set up
their first two conditions and drag it across the row.
Then I could present them with a copy of MS's conditional
formatting dialog (for consistency and ease of use) and
they could set up their secondary criteria. The secondary
criteria would only be applied to each cell if the primary
criteria hadn't changed the formatting. I hope this makes
sense.

Can I do this? Can I use Microsoft's form so that I can
get the cell preview, etc. and use the user's entered
values to replace the conditional formatting on only
certain cells?

TIA

~~Jody

 

[Tom Ogilvy]

The conditional formatting dialog will display the formatting that already
exists in the selected cell when it is shown, same as if you showed it from
the menu. If you haven't used all three conditions you can add conditions.
If you have used all three, you can edit the current condtions (or even if
you have less than 3, you can still edit existing).

application.Dialogs(xlDialogConditionalFormatting).Show