I am struggling with this code please help me. I want to record change in sheet this code does that but it goes cell by cell which makes it slower. my active sheet runs on 200ms which is very fast by the time this code does its job values changed. is there any way that display values at the same time not by going to each cell. i have so much confusion in my mind please pardon me if my request did not even made any sense.
Private Sub Worksheet_Change(ByVal Target As Range)
Const Scope = "G9:H9,G11:H11,G13:H13,G15:H15,G17:H17,G19:H19,G21:H21,G23:H23,G25:H25,G27:H27,G29:H29,G31:H31,G33:H33,G35:H35,G37:H37,G39:H39,G41:H41,G43:H43,G45:H45,G47:H47,G49:H49,G51:H151,G53:H53,G55:H55,G57:H57,G59:H59,G61:H61,G63:H63,G65:H65,G67:H67" ' monitoring area
Static oData As New Dictionary
Dim rCells As Range
Dim oCell
Dim dDelta
Set rCells = Application.Intersect(Target, Target.Parent.Range(Scope))
If Not rCells Is Nothing Then
For Each oCell In rCells
With oCell
dDelta = oData(.Address)
.Offset(0, 1).Value = dDelta
oData(.Address) = .Value
End With
Next
End If
End Sub
Have you tried turning off Screen Updating? Add this line right under your Sub line and see if that helps with the speed at all.
Application.ScreenUpdating = False