Private Sub Worksheet_Change(ByVal Target As Range)

[David Evans]

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]

Insert a module,then set up a public variable arr

'Module 1
Public arr

'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

'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

 

[wudixin96]

Before its working,you must reopen the workbook

 

[Hui]

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:

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]

 

[wudixin96]

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:

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

 

[Hui]

Good Point

Change O7 to: =SUMPRODUCT(--(M9:M84<>O9:O84))

 

[David Evans]

Thanks Gents - two great solutions. I appreciate your help greatly. Enjoy your weekend!