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]

Install the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target.Interior.ColorIndex = 4
Application.EnableEvents = True
End Sub



Because it is worksheet code, it is very easy to install and automatic to use:


1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window


If you have any concerns, first try it on a trial worksheet.


If you save the workbook, the macro will be saved with it.




To remove the macro:


1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window


To learn more about macros in general, see:


Getting Started with Macros and User Defined Functions


To learn more about Event Macros (worksheet code), see:


Event Macros, Worksheet Events and Workbook Events

Macros must be anabled for this to work.

 

[stevejauch]

Install the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target.Interior.ColorIndex = 4
Application.EnableEvents = True
End Sub



Because it is worksheet code, it is very easy to install and automatic to use:


1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window


If you have any concerns, first try it on a trial worksheet.


If you save the workbook, the macro will be saved with it.




To remove the macro:


1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window


To learn more about macros in general, see:


Getting Started with Macros and User Defined Functions


To learn more about Event Macros (worksheet code), see:


Event Macros, Worksheet Events and Workbook Events

Macros must be anabled for this to work.

OK, I did it and it worked... once. And only on the cell my cursor was on when I typed in that macro. It correctly changed the cell color to green but none of the other cells changed. I have a range (C26:M40) where I want this action to happen. Any number of the cells in that range may change and I'd like them all to turn color.

Now I can't get any of the cells in that range (including the original one that did change) to change colors. I used the Format Painter paintbrush to reset all the colors back to the original but no cell content updates affects the color.

Thanks for your patience - I really appreciate it.

steve

 

[Gary's Student]

O.K then:

1. Remove the old macro
2. Save the file
3. Close the file
4. Re-open the file
5. Verify auto-change-color is no longer active
6. Install this version and test:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, r As Range
Set Rng = Range("C26:M40")
If Intersect(Target, Rng) Is Nothing Then Exit Sub
For Each r In Target
    Application.EnableEvents = False
        r.Interior.ColorIndex = 4
    Application.EnableEvents = True
Next
End Sub

 

[stevejauch]

Progress! But I'm still experiencing a few glitches which raise some questions:
1) MINOR ISSUE: If a cell is "updated" but the value itself doesn't really change (e.g. C30 already was 5 and the user re-entered 5) the cell still changes color.
2) Using the Format Painter paintbrush icon (Clipboard ribbon) to manually change (or reset) colors of the cells in the range, still runs the macro changing the color back. Weird but changing the cell color using the paint can fill icon (Font ribbon) works fine. Any chance we can tell the macro to "ignore" the Format Painter icon?
3) Users will need to insert columns expanding (or shrinking) that range. I instruct the users to have their cursor on Column M when inserting new columns to maintain the integrity of summation formulas. When I insert a column though, the entire column (Rows 1:1048576) gets colored green. Any chance we can limit the macro to only affect the cells inside the range?
4) Similarly, I see that even when I insert columns and the range changes, the range designation in the macro still remains C20:M40. Any chance this can be made to be a relative reference so as users add columns the macro range is appropriately expanded?

I suppose I could instill a couple workarounds (i.e. don't enable the macro when doing certain actions) (i.e. add a bunch of columns but hide them so instead of inserting new columns users would just unhide them) but I'd love to program it properly if it's possible.

I so very much appreciate your help.

 

[Gary's Student]

I can definitely fix #3 & #4.
Check back later.

 

[stevejauch]

Thanks - looking forward to hearing back from you!

 

[Gary's Student]

This should fix issue #3:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, r As Range
Set Rng = Range("C26:M40")
If Intersect(Target, Rng) Is Nothing Then Exit Sub
[COLOR=#008080][B]For Each r In Intersect(Target, Rng)[/B][/COLOR]
    Application.EnableEvents = False
        r.Interior.ColorIndex = 4
    Application.EnableEvents = True
Next
End Sub

 

[stevejauch]

Perfect - worked! #3 is done.

 

[stevejauch]

Thanks Gary's Student for all your help. Anything yet on any of the other three items:
1) If a cell is "updated" but the value itself doesn't really change (e.g. C30 already was 5 and the user re-entered 5) the cell still changes color.
2) Using the Format Painter paintbrush icon (Clipboard ribbon) to manually change (or reset) colors of the cells in the range, still runs the macro changing the color back. Weird but changing the cell color using the paint can fill icon (Font ribbon) works fine. Any chance we can tell the macro to "ignore" the Format Painter icon?
DONE - 3) Users will need to insert columns expanding (or shrinking) that range. I instruct the users to have their cursor on Column M when inserting new columns to maintain the integrity of summation formulas. When I insert a column though, the entire column (Rows 1:1048576) gets colored green. Any chance we can limit the macro to only affect the cells inside the range?
4) Similarly, I see that even when I insert columns and the range changes, the range designation in the macro still remains C20:M40. Any chance this can be made to be a relative reference so as users add columns the macro range is appropriately expanded?

If the others are too hard, I'll just develop around them and update my end user training. Thanks!