Change Cell color when cell is edited

[brianv]

We have multiple personell needing to constantly update a scheduling sheet that we all use with one another, I am in need to have excel automatically change the cell background color when a cell is edited, but the cell can be forced back to its original color (or any color for that matter) when the edited changes have been agreed to. (We simply want to know what the current changes are by visual representation.)

For example: When the workbook is opened, normally the cell backgrounds are grey, however, if John enters in new information in cell D4, then that cell automatically turns yellow. Consider it "Pending information", but once the change is agreed to, then Bill (or anyone for that matter) can force the cell color back to grey. (Visually then its "Current" again). We are not protecting the cells perse', we simply want to 'visually represent' recent changes.

BrianV

 

[Tazguy37]

Using Tools, Track Changes isn't an option you can use?

 

[brianv]

'Track changes' is to subtle of tracking, it generally will put a marker in the upper left corner of the cell and to us is easily missed.

The person editing could force the color to yellow, but again what if he forgets to do this...

BrianV

 

[Tazguy37]

If this is only going to be used on one particular sheet, then you could use the following code to do this:

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

If you need it on all sheets in the workbook, then:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
    Target.Interior.ColorIndex = 6
    Application.EnableEvents = True
End Sub

will work. The sheet code would go in the particular sheet's code module. The workbook code will go in the "ThisWorkbook" module. Make sense? Hope that helps!

Press Alt-F11 to open the VBE.
Press Control-R to open the Project Explorer.
Click "Microsoft Excel Objects" for the file you're working on.
Double-click the sheet where you need this to work. (Or "ThisWorkbook")
Open the Code pane with F7.
Paste the above code in.
Press Alt-Q to close the VBE and return to Excel.

 

[brianv]

TazGuy,
That is EXACTLY what I wanted...Perfect.

Thanks
BrianV

 

[HalfAce]

Hello brianv,
You can take Taz's fine solution one step further by doing this.
(Works on columns D & E. It should be easy enough to see how to change it to any column(s) you like, and/or set it up for all sheets like Taz showed you. Certainly post back if it's not.)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ColorRng As Range
Set ColorRng = Range("D:E")

If Not Intersect(Target, ColorRng) Is Nothing Then
    Ans = MsgBox("Has this information been confirmed?", 4, "Information Pending or Confirmed?")
    If Ans = vbYes Then
        Target.Interior.ColorIndex = 15 'Light grey
      Else
        Target.Interior.ColorIndex = 6 'Yellow
    End If
End If

End Sub

Hope it helps,
(Hi Taz! )
Dan

 

[Megsnd]

If this is only going to be used on one particular sheet, then you could use the following code to do this:

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

If you need it on all sheets in the workbook, then:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
    Target.Interior.ColorIndex = 6
    Application.EnableEvents = True
End Sub

will work. The sheet code would go in the particular sheet's code module. The workbook code will go in the "ThisWorkbook" module. Make sense? Hope that helps!

Press Alt-F11 to open the VBE.
Press Control-R to open the Project Explorer.
Click "Microsoft Excel Objects" for the file you're working on.
Double-click the sheet where you need this to work. (Or "ThisWorkbook")
Open the Code pane with F7.
Paste the above code in.
Press Alt-Q to close the VBE and return to Excel.

Hello! I also need t use this code. However, I need some way to change the colors back once I have made the updates. I will be the only one changing the color back so even if it was a code that I can save somewhere and just paste it in when I need it, that would be extremely helpful!