"Freezing" Conditional Formatting

[LarryP]

Excel 2003/Windows XP -- tricky situation encountered. I have a complex
sequence of macros that, among other things, uses conditional formatting to
colorize two columns of data (Col F and Col G). That works fine. However,
one of the later steps in the sequence may shuffle columns around based on a
user-defined template. Sometimes this means the data from Cols F/G moves to
Cols X/Y or whatever. But the conditional formatting remains back in Cols
F/G and gets applied to whatever data resides in those columns after the
shuffle, producing nonsense. Does anyone know a way I can let the
conditional formatting do its thing, but then "freeze" the resulting colors
so if the data moves, the colors go with it?

 

[Norman Jones]

Hi Larrry,

Perhaps try using relative references
in the Conditional Format rules.

 

[LarryP]

Don't think I can solve it that way, since the two columns could wind up
anywhere after the column-shuffling step -- I wouldn't know how to set the
relative references. One user might move F/G out to X/Y, the next user might
leave them as F/G, and the third user might split them up as C and Q.

 

[Norman Jones]

Hi Larry,

As an experiment, select cells B1:B5,
and enter the (relative) Condional Format
formula:

A1>0

and choose a fill colour.

Now cut and paste the column A cells,
or the column B cells ...

 

[LarryP]

I solved this problem by abandoning colorization-by-conditional-formatting
and instead using looping For-Each-cel code to "hard-set" colors based on
cell content. But for the long haul I'd still be interested to know if
there's a way to capture the current result of a conditional format (color,
font, or whatever) and use it to perform some action or make some decision.
While grappling with this I used the immediate window to query the interior
colorindex of one of the conditionally formatted cells, and it returned a "no
color" answer, so if the color value produced by CF is stored anywhere at
all, it sure ain't in interior.colorindex or interior.color.