Speeding up Data Validation macro

  • Thread starter Thread starter Ryan
  • Start date Start date
R

Ryan

Hi,

I'm a novice at VBA and I'd appreciate some pointers on how this macro I've
written (well, recorded mostly) could be made to run faster. I'm reading the
defined range D7:CU213 on 84 sheets and looking for cells which aren't protected
(sub totals are protected)to apply some Data Validation to. At the moment it's
taking about 4 minutes per sheet, which seems a tad slow to me for such a small
range.

Anyways, here's the code, any help most appreciated.

-------------------------------------------
Sub DataVal()
' Loops through Range, tests for locked cells, applies Data Validation to
unlocked cells.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
ActiveSheet.Unprotect

For Each C In ActiveSheet.Range("D7:CU213").Cells
If C.Locked = False Then
C.Select
With Selection.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween, Formula1:="0", Formula2:="99999"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Invalid Input"
.InputMessage = ""
.ErrorMessage = "Input value must be numeric."
.ShowInput = False
.ShowError = True
End With
End If
Next

ActiveSheet.Protect
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub
 
Back
Top