Hi,
I have written some basic code that updates a cell based on a Worksheet_Change event in a corresponding row.
The Code below roughly works but sometimes crashes Excel or takes a few seconds to complete which suggests it's looping or something!
The code logic is as follows:
If A1 changes to = 1 Then Unlock B1 and clear formula contents
If A1 changes to <> 1 Then Copy/Paste E1 (CellFormula) to B1 and Lock B1 cells
Any help would be much appreciated! I've attached a sample worksheet which includes the Code below.
Code
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Errorline
Dim Toggle As Range
Dim Cell As Range
Dim CellFormula As Range
With Target
Set Toggle = Range("a" & .Row)
Set Cell = Range("b" & .Row)
Set CellFormula = Range("CellFormula")
If Toggle = 1 Then
Cell.Locked = False
Cell.Clear
Else
CellFormula.Copy
Cell.Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
Cell.Locked = True
End If
End With
Errorline:
End Sub
Display More