Highlight changes is orginial cell value has changed

[jojojojo5]

I would like to know how to using conditional formatting OR VBA code to highlight the changes in a cell that has changed from it's orginial value. I've tried the track changes option , but for some reason I can can not get it to print and I have to set my options to a shared workbook to enable it.

I prepare a weekly planing workbook and would like to have the workbook highlight any changes made. The workaround file I have now is too big:-( Any help is appreciated:-)

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Interior.ColorIndex = 38
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jojojojo5" <[email protected]> wrote
in message news:[email protected]...
>
> I would like to know how to using conditional formatting OR VBA code to
> highlight the changes in a cell that has changed from it's orginial
> value. I've tried the track changes option , but for some reason I can
> can not get it to print and I have to set my options to a shared
> workbook to enable it.
>
> I prepare a weekly planing workbook and would like to have the workbook
> highlight any changes made. The workaround file I have now is too
> big:-( Any help is appreciated:-)
>
>
> --
> jojojojo5
> ------------------------------------------------------------------------
> jojojojo5's Profile:
http://www.excelforum.com/member.php...o&userid=36719
> View this thread: http://www.excelforum.com/showthread...hreadid=564480
>

[jojojojo5]

THANK YOU SO MUCH, I knew someone out there had the answer. I have one maybe two more questions:-)

1. How do you modify this macro so you can turn it ON and OFF?


Attached below is another macro (I've also attached the file with the macros) that documents the changes on a hidden worksheet, with these to macros combined I'm basically recreating the 'Track Changes" feature in excel except I'm not losing an excel features that a "Shared Workbook" can't provide.

2. How do I modify this code to add the "User Name" and "SheetName"?

This code goes in the "Workbook" module and the other code is inserted into each worksheet.

Dim vOldVal 'Must be at top of module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim bBold As Boolean

If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
bBold = Target.HasFormula
Application.EnableEvents = False
With Sheet1
.Protect Password:="Secret", UserInterFaceOnly:=True
.Cells(1, 1) = "CELL CHANGED"
.Cells(65536, 1).End(xlUp)(2, 1) = Target.Address
.Cells(1, 2) = "OLD VALUE"
.Cells(65536, 2).End(xlUp)(2, 1) = vOldVal
With .Cells(1, 3)
.Value = "NEW VALUE"
End With
With .Cells(65536, 3).End(xlUp)(2, 1)
.Value = Target
.Font.Bold = bBold
End With
.Cells(1, 4) = "TIME OF CHANGE"
.Cells(65536, 4).End(xlUp)(2, 1) = Time
.Cells(1, 5) = "DATE OF CHANGE"
.Cells(65536, 5).End(xlUp)(2, 1) = Date
.Cells.Columns.AutoFit
End With
vOldVal = vbNullString
Application.EnableEvents = True
On Error GoTo 0
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
vOldVal = Target
End Sub


Any help is greatly appreciated!!!!!

[Bob Phillips]

"jojojojo5" <[email protected]> wrote
in message news:[email protected]...
>
> THANK YOU SO MUCH, I knew someone out there had the answer. I have one
> maybe two more questions:-)
>
> 1. How do you modify this macro so you can turn it ON and OFF?

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:H10"
Const fON As Boolean = TRUE '<=== change this

On Error GoTo ws_exit:
if fON Then
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Interior.ColorIndex = 38
End With
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub


> Attached below is another macro (I've also attached the file with the
> macros) that documents the changes on a hidden worksheet, with these to
> macros combined I'm basically recreating the 'Track Changes" feature in
> excel except I'm not losing an excel features that a "Shared Workbook"
> can't provide.
>
> 2. How do I modify this code to add the "User Name" and "SheetName"?


Option Explicit

Dim vOldVal 'Must be at top of module

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
Dim bBold As Boolean
Dim iLastRow As Long

If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
bBold = Target.HasFormula
Application.EnableEvents = False
With Sheet1
.Cells(1, "A").Value = "CELL CHANGED"
.Cells(1, "B").Value = "OLD VALUE"
.Cells(1, "C").Value = "NEW VALUE"
.Cells(1, "D").Value = "TIME OF CHANGE"
.Cells(1, "E").Value = "DATE OF CHANGE"
.Cells(1, "F").Value = "USER"
.Cells(1, "G").Value = "SHEET"
iLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Protect Password:="Secret", UserInterFaceOnly:=True
.Cells(iLastRow, "A").Value = Target.Address
.Cells(iLastRow, "B").Value = vOldVal
With .Cells(iLastRow, "C")
.Value = Target
.Font.Bold = bBold
End With
.Cells(iLastRow, "D").Value = Time
.Cells(iLastRow, "E").Value = Date
.Cells(iLastRow, "F").Value = Environ("UserName")
.Cells(iLastRow, "G").Value = Me.Name
.Cells.Columns.AutoFit
End With
vOldVal = vbNullString
Application.EnableEvents = True
On Error GoTo 0
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
vOldVal = Target
End Sub

[Rory Murphy]

That worked great for myself. Quick question though.
I've selected D2:H10 to highlight in yellow if there is a change to that specific range.

How can I now add code which will allow any changes in the entire sheet to highlight in red?

So changes to entire sheet in red
Changes to specific range yellow

Thanks
Rory