Link to home
Start Free TrialLog in
Avatar of mohantyd
mohantydFlag for India

asked on

Require your help for highlighted field

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
 
 
 
    With Application
 
         .ScreenUpdating = False
 
         .EnableEvents = False
 
    End With
 
 
 
    If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
 
    bBold = Target.HasFormula
 
        With Sheet1
 
            .Unprotect Password:="Secret"
 
                If .Range("A1") = vbNullString Then
 
                    .Range("A1:E1") = Array("CELL CHANGED", "OLD VALUE", _
                        "NEW VALUE", "TIME OF CHANGE", "DATE OF CHANGE" “ USER WHO MADE CHANGE”)
 
                End If
 
 
 
 
 
            With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
 
                  .Value = Target.Address
 
                  .Offset(0, 1) = vOldVal
 
                      With .Offset(0, 2)
 
                        If bBold = True Then
 
                          .ClearComments
 
                          .AddComment.Text Text:= _
"OzGrid.com:" & Chr(10) & "" & Chr(10) & _
                                  "Bold values are the results of formulas"
 
                        End If
 
                          .Value = Target
 
                          .Font.Bold = bBold
 
                      End With
 
 
 
                .Offset(0, 3) = Time
 
                .Offset(0, 4) = Date
 
            End With
 
            .Cells.Columns.AutoFit
 
            .Protect Password:="Secret"
 
        End With
 
    vOldVal = vbNullString
 
    End With
 
.user = Target
 
    With Application
 
         .ScreenUpdating = True
 
         .EnableEvents = True
 
    End With
 
 
 
On Error GoTo 0
 
 
 
End Sub
 
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

Hello,

could you flesh out the question a bit? Is something not working? Is a line of code giving you trouble? If so, what? Where? Any error message?

cheers, teylyn
ASKER CERTIFIED SOLUTION
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial