• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Private Sub Worksheet_Change(ByVal Target As Range)

David Evans

Active Member
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("M9:M84")) Is Nothing Then Exit Sub
Range("B2").Value = Now()
Range("B2").NumberFormat = "mm/dd/yyyy hh:mm:ss"
End Sub
I want the value in B2 to be updated anytime a value in Range("M9:M84") changes. This range is updated from formula and not by the user making changes. My 'update" appears to only work if I edit one of the cells in the range ....

Any ideas Folks?
 

wudixin96

Member
Insert a module,then set up a public variable arr
Code:
'Module 1
Public arr
Code:
'ThisWorkBook

Private Sub Workbook_Open()
    arr = ThisWorkbook.Worksheets("Sheet1").Range("M9:M84")
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    arr = ThisWorkbook.Worksheets("Sheet1").Range("M9:M84")
End Sub
Code:
'Sheet1

Private Sub Worksheet_Calculate()
    Dim arrTemp, i
    arrTemp = ThisWorkbook.Worksheets("Sheet1").Range("M9:M84")
    For i = LBound(arr) To UBound(arr)
        If arr(i, 1) <> arrTemp(i, 1) Then
            With Range("B2")
                .NumberFormatLocal = "mm/dd/yyyy hh:mm:ss"
                .Value = Now
            End With
            Exit For
        End If
    Next
    arr = ThisWorkbook.Worksheets("Sheet1").Range("M9:M84")
End Sub
 
Last edited:

Hui

Excel Ninja
Staff member
Find a Blank Column where nobody will intereact
I am using Column O but you may want to use AA or something way acrross there

Copy M9:M84 and paste as values in O9:O84
In O7 =SUMPRODUCT(--(M9:M84<>O9:O84))

Then use this code in the worksheet code module:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If [O7] = 0 Then Exit Sub

Application.EnableEvents = False
Range("B2").Value = Now()
Range("B2").NumberFormat = "mm/dd/yyyy hh:mm:ss"
Application.EnableEvents = True

Range("M9:M84").Copy
Range("O9:O84").PasteSpecial _
  Paste:=xlPasteValues, _
  Operation:=xlNone, _
  SkipBlanks:=False, _
  Transpose:=False
Application.CutCopyMode = False

End Sub
see attached file:

[updated as per comments below]
 

Attachments

Last edited:

wudixin96

Member
Find a Blank Column where nobody will intereact
I am using Column O but you may want to use AA or something way acrross there

Copy M9:M84 and paste as values in O9:O84
In O7 =SUM(O9:O84)-SUM(M9:M84)

Then use this code in the worksheet code module:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If [O7] = 0 Then Exit Sub

Application.EnableEvents = False
Range("B2").Value = Now()
Range("B2").NumberFormat = "mm/dd/yyyy hh:mm:ss"
Application.EnableEvents = True

Range("M9:M84").Copy
Range("O9:O84").PasteSpecial _
  Paste:=xlPasteValues, _
  Operation:=xlNone, _
  SkipBlanks:=False, _
  Transpose:=False
Application.CutCopyMode = False

End Sub
see attached file:
How about if the value of range("M9:M84") is text
 
Top