Thanks for the tip rorya,
I plugged this code snippet straight in, but unfortunately I'm still getting a run-time error
:
Run-time error 1004:
Application-defined or object-defined error
If it helps at all, here's the surrounding code that runs when the numeric value in $A$1 is changed (BTW, I'm using 2010 edition):
ActiveSheet.Unprotect Password:="a.password"
'Clean-up any pre-existing cell content if NumberOfUsersInBatch is reduced
Cell_Range_To_Clear = "D" & NumberOfUsersInBatch + 7 & ":E106"
Range(Cell_Range_To_Clear) = ""
'Unlock appropriate cells based on NumberOfUsersInBatch
Cell_Range_To_Unprotect = "D7" & ":F" & NumberOfUsersInBatch + 7 - 1
Range(Cell_Range_To_Unprotect).Select
Selection.Locked = False
Selection.FormulaHidden = False
'IDEALLY, APPLY THE DEFAULT FORMULA TO ALL CELLS AT ONCE
'IF NOT POSSIBLE, USE A FOR / WHILE LOOP
Cell_Range_To_Reset = "F" & NumberOfAgentsInBatch + 7 & ":F106"
Range("F10").Select
Range("F10").Formula = "=IF(ISNUMBER(C10,""DefaultText"","""")"
'Lock all other cells
Cell_Range_To_Protect = "D" & NumberOfAgentsInBatch + 7 & ":F106"
Range(Cell_Range_To_Protect).Select
Selection.Locked = "True"
Selection.FormulaHidden = "True"
ActiveSheet.Protect Password:="a.password"