"Freezing" Conditional Formatting
 

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?

"Freezing" Conditional Formatting
 

Hi Larrry,

Perhaps try using relative references
in the Conditional Format rules.



---
Regards.
Norman
"LarryP" wrote in message
...
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?

"Freezing" Conditional Formatting
 

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" wrote:

Hi Larrry,

Perhaps try using relative references
in the Conditional Format rules.



---
Regards.
Norman
"LarryP" wrote in message
...
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?

"Freezing" Conditional Formatting
 

Hi Larry,

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

A10

and choose a fill colour.

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


---
Regards.
Norman


"LarryP" wrote in message
...
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" wrote:

Hi Larrry,

Perhaps try using relative references
in the Conditional Format rules.



---
Regards.
Norman
"LarryP" wrote in message
...
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?

"Freezing" Conditional Formatting
 

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.


"Norman Jones" wrote:

Hi Larry,

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

A10

and choose a fill colour.

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


---
Regards.
Norman


"LarryP" wrote in message
...
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" wrote:

Hi Larrry,

Perhaps try using relative references
in the Conditional Format rules.



---
Regards.
Norman
"LarryP" wrote in message
...
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?