Formatting paste links

QuestionEdit

QUESTION: Ihave a summary page with links to cells in about 30 different sheets. Since I am using97 I only have 3 conditional formatting options, but am coding cells for about 12 different possiblities (holidays, sick days, weekends, etc). I have used Case in VBA on the individual sheets. The pasted cell values show up Ok on the summary page, but the formatting doesn't follow them. Since it's a paste link function, it doesn't seem to read the cell value change. Is there simple coding I can use to have the summary page pick up the formatting?

ANSWER: I haven't dealt with the need to do this so I don't have a good suggestion for you, but one would be---You can use a copy/paste special/all and it will bring over both the value and the formatting. If you do this in the form of a macro you can automate the process to make the copy paste quicker. It will be tedious to set up, but once set up this should work for you.

Hope this helps.

---------- FOLLOW-UP ----------

QUESTION: Thanks for responding. That does seem tedious,though. Is there a brief code I can insert that will run a routine when I switch back to the summary page. In other words, do the entries on the separate worksheets, but when the summary page is activated, the code will run?

AnswerEdit

I was suggesting that you set up a macro that will do the copy paste special from each of the sheets to the summary all at one time whenever the macro is run. Once set up- whenever the macro is run-it will do the copy paste special from all of the sheets to the summary sheet. I'm sure it can be set up to run whenever the summary sheet is accessed, but I don't know how to do that. Otherwise you can run the macro in the usual way by using the macro run keys that you set up when you write the macro, something like control A (ctrla) etc. You can also assign the macro to a button and have it run whenever you click the button (see the help for this).

Richard