Changes to Data on a Worksheet

  • Thread starter Thread starter bc
  • Start date Start date
B

bc

I have a data entry sheet (Excel 2000) and a "store" sheet. The info
on the data entry sheet is saved on a single row in the store sheet. I
need to detect whether any changes have been made to the details on
the data entry page and currently do this by concatenating every data
cell on the data sheet and comparing it to the concatenated cells on
the corresponding row in the store. Is there an easier way to detect
whether any changes have been made on the data entry page?

Thanks for any help.
Brian
 
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1" '<== change to suit
Dim cell As Range
Dim rng As Range
Dim i As Long

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

For i = 2 To Me.Cells(Me.Rows.Count, "A").End(xlUp).Row

If Me.Cells(i, "A").Value < .Value Then

If rng Is Nothing Then

Set rng = Me.Rows(i)
Else

Set rng = Union(Me.Rows(i), rng)
End If
End If
Next i

If Not rng Is Nothing Then rng.Delete
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


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top