Link both format and values from one worksheet to another.

**abfarris** · 06-05-2009, 11:25 AM

I am currently needing to link both the values and formats of cells from one worksheet to another. I don't think i can use conditional formatting, I have several cells that need to be blue, and others that need to be black. These cells will change from blue to black after they are approved. I have one sheet that is for working with (has several formats, to make it easy to find what is needed) and the other is to be clean and neat for printing. I was needing to have the color in the cells change on the clean sheet as the colors change on the working sheet. Is there a way I can do this through VBA? I also need to password protect the clean sheet.

**abfarris** · 06-05-2009, 11:34 AM

Ok sorry guys, I think I found out how to make a Macro that will do that, I was hoping i could put something in automatically that would make it change, but as of now I guess I can just rerun the macro each time i need an update. But if anyone knows a better idea of how to link them together to have changes appear automatically that would be even better, and I'd love to know.

thanks!

**davegugg** · 06-05-2009, 11:42 AM

You could put your macro in on of the Workbook events. For example, if you put it in the before save event, before saving it will run the macro updating your values and formats. Hope that helps.

**abfarris** · 06-05-2009, 11:56 AM

How do you get to events? Haven't used that before.

**davegugg** · 06-05-2009, 12:10 PM

In the Visual Basic Editor, first double click the "ThisWorkbook" Microsoft Excel Object under the VBAProject file structure on the left. Then there are two combo boxes on top of where you put the code in. Change the one that says "(General)" to Workbook. Then the other usually flips to "Open". Change it to whichever event you want. For this purpose I suggest BeforeSave. It will run the macro any time you save, before the save process, so the changes will be saved.