Macro that changes the cell colour when new text entered

[XLnovice2]

In a previous job I used a spreadsheet that had a macro which changed the cell colour to yellow each time new text was entered or changes made. This was so handy b/c it allowed you to see what changes people had made to a spreadsheet when they sent it back to you. My recent Excel course instructor didn't know how to do it and didn't seem to think it was a macro. I'm hoping someone out there might know how I record this handy macro?

 

[iggydarsa]

I dont think it is the same concept of macros you two are talking about... I dont know what kind of a code you used but it sounds like this code is placed in

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

. This is an EVENT, and it gets activated when a specific event occurs. If you write that code in a module and save as a macro, it doesnt sound like it would work properly...

Regards

 

[Joe Was]

Note where you store each of these!
The Event is for the ThisWorkbook module only!
The Re-Set is to be run from a Standard module!


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'ThisWorkbook module code!

If Target.Value > xlNull Then
Target.Interior.ColorIndex = 36
Else
Exit Sub
End If
End Sub



Sub reSetCellColors()
'Standard Module code, like: Module1.
Dim ws As Worksheet

For Each ws In Worksheets
ws.Select
Cells.Select
Selection.Interior.ColorIndex = xlNone
Range("A1").Select
Next ws
End Sub

 

[XLnovice2]

Thanks Joe Was, but as a novice that's like a foreign language to me! If I showed one of our staff who's good with formulas do you think they'll understand it and be able to implement?

 

[joelnichols]

Pretty cool Joe. I notice it highlights any cell that was "used" not just changed. Example: A5 has "dog" in it and you re-enter "dog" it wasnt really changed but is still highlighted. Is there a way to make the code work only if the new data differs from the old data?

 

[Joe Was]

It is not a formula it is Code [VBA code]!


Open the workbook that you want it to run in.

Hit:

Alt+F11

Hit:

Ctrl+R

Select by double clicking "ThisWorkbook" in the list of Modules in the Project list. Paste a copy of this code, into the Code page that opens.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'ThisWorkbook module code!

If Target.Value > xlNull Then
Target.Interior.ColorIndex = 36
Else
Exit Sub
End If
End Sub


Then from the toolbar select: Insert - Module and Paste a copy of this code there:


Sub reSetCellColor()
'Standard Module code, like: Module1.
Dim ws As Worksheet

For Each ws In Worksheets
ws.Select
Cells.Select
Selection.Interior.ColorIndex = xlNone
Range("A1").Select
Next ws
End Sub


Click the Upper most upper-right Close "X" to return to your Sheet.

Any changes will now be colored light yellow.

To reset all cell colors back to none:

From the Excel toolbar: Tools - Macro - Macros...
Select the macro and "Run"

Note if wile in the Macro selection Utility you select Options, you can assign a Hot-Key to the Macro, like Ctrl+z

Then to re-set all the cell colors you can just hit: Ctrl+z without needing to go through all the toolbar menus!

 

[Joe Was]

Deleted: Board is acting up?

 

[Joe Was]

Yes you can, but everything will run slow and your application will become a memory hog!

You need to store each change in a variable and compare to the next change. if the user goes back to a cell that was worked with a cycle back it will not detect the old value.

To track all changes you need to back-up each sheet as a copy and check to see that a cell changed and that the Target Value is different from the backup copy.

So I would not do it unless your application only uses a few cells of data!


Try this change [only works for one cycle in Each Cell!] if you type the same value that is in the cell it will not color!
Only new values or changes will trigger the cell color!


Public myOld As Variant

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'ThisWorkbook module code!

If (Target.Value > xlNull And Target.Value <> myOld) Then
Target.Interior.ColorIndex = 36
Else
Exit Sub
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'ThisWorkbook module code!

If Target.Cells.Count > 1 Then Exit Sub
myOld = Target.Value
End Sub

 

[XLnovice2]

Thanks very much Joe Was, it worked! This will be of great assistance to me on a daily basis so I really appreciate your help. We're not worthy!