Change cell's color when any value changes

[stevejauch]

Needing to pass/email a Workbook to another colleague weekly where only a few cell values may change but it's those changed values that drive the work this colleague performs. Track Changes doesn't quite do what I need or I'm doing it very wrong. I've never written a Macro or used Visual Basic but I did just enable the Developer tab. There's quite a number of articles (thanks Google) that appear relevant but I am more newbie than the level many of those articles are written for. Can someone give very step-by-step instructions for me (or at least be patient as I may ask more clarifying questions). I'm using Excel 2007 on a Windows7 machine and this Workbook has 4-5 individual Worksheets and I have quite a number of formulas referencing cells in other Worksheets.

Thanks - steve

 

[Gary's Student]

Yes...If you change the format of a cell by using Format > Format Cells...efrom the Home tab, the macro will remain "sleeping" and your formatting will stick. If, however, you attempt to change formatting using the Format Painter, the macro will "wake up" and over-ride what you have done.

So to take charge and apply your own formats, don't use the Painter..

I have made no progress on expanding or shrinking the area of interest (might involve creating and managing a dynamic Named Range) , or issue #1....................perhaps its time to open a new question so we get inputs from other experienced users.

 

[cleenstar]

Yes...If you change the format of a cell by using Format > Format Cells...efrom the Home tab, the macro will remain "sleeping" and your formatting will stick. If, however, you attempt to change formatting using the Format Painter, the macro will "wake up" and over-ride what you have done.

So to take charge and apply your own formats, don't use the Painter..

I have made no progress on expanding or shrinking the area of interest (might involve creating and managing a dynamic Named Range) , or issue #1....................perhaps its time to open a new question so we get inputs from other experienced users.

Hey Gary's Student!!
I found the Macros you provide very helpful!! Any chance you have a solution that wouldn't run the macro until after the initial inputs are in each cell? So...would love to track changes that happen after initial input, but NOT from blank cell to first input. Let me know!!
Thanks so much
- Colleen