S
Sandy
I have the following fairly simple code which runs fairly slowly, could it
be because I have a lot of Conditional Formatting going on at the same time?
and if so , would I be better incorporating the conditional formatting into
my code to acheive better processing?
Sandy
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Data Input").Unprotect Password:=""
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each mycell In Range("C9:K9,M9:U9")
If mycell.Value = 3 Then
With mycell.Offset(5)
.Value = "Good"
.Validation.Delete
End With
ElseIf mycell.Value <> 3 And mycell.Offset(5).Value = "Good" Then
With mycell.Offset(5)
.Value = "Hit"
With .Validation
.Delete
.Add Type:=xlValidateList,
Formula1:="Left,Right,Short,Long"
.IgnoreBlank = True
.InCellDropdown = True
End With
End With
ElseIf mycell.Value = "" Then
With mycell.Offset(5)
.Value = "Hit"
With .Validation
.Delete
.Add Type:=xlValidateList,
Formula1:="Left,Right,Short,Long"
.IgnoreBlank = True
.InCellDropdown = True
End With
End With
End If
Next
'MsgBox "Hi"
Application.EnableEvents = True
Application.ScreenUpdating = True
Sheets("Data Input").Protect Password:=""
End Sub
be because I have a lot of Conditional Formatting going on at the same time?
and if so , would I be better incorporating the conditional formatting into
my code to acheive better processing?
Sandy
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Data Input").Unprotect Password:=""
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each mycell In Range("C9:K9,M9:U9")
If mycell.Value = 3 Then
With mycell.Offset(5)
.Value = "Good"
.Validation.Delete
End With
ElseIf mycell.Value <> 3 And mycell.Offset(5).Value = "Good" Then
With mycell.Offset(5)
.Value = "Hit"
With .Validation
.Delete
.Add Type:=xlValidateList,
Formula1:="Left,Right,Short,Long"
.IgnoreBlank = True
.InCellDropdown = True
End With
End With
ElseIf mycell.Value = "" Then
With mycell.Offset(5)
.Value = "Hit"
With .Validation
.Delete
.Add Type:=xlValidateList,
Formula1:="Left,Right,Short,Long"
.IgnoreBlank = True
.InCellDropdown = True
End With
End With
End If
Next
'MsgBox "Hi"
Application.EnableEvents = True
Application.ScreenUpdating = True
Sheets("Data Input").Protect Password:=""
End Sub