Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting on "Any Text" entered in a cell | Excel Discussion (Misc queries) | |||
Conditional Formatting - find cells containg "+" | Excel Discussion (Misc queries) | |||
Excel 2003: Conditional Formatting using "MIN" & "MAX" function | Excel Discussion (Misc queries) | |||
conditional formatting "if part of cell contents contains string" | Excel Worksheet Functions | |||
excel should "paste special" a "conditional formatting" | Excel Programming |