Newbie Help required with Excel Loops 1

[hopelessliar]

I have a macro that effectively protects a cell from being changed (see below) which I simply recorded. What I want to do, is turn this into a loop so that where A69 is selected and compared against A18, A70 will be compared to A19 and so on. I KNOW this is simple but I can't get the hang of it so any help would be greatfully received.

Thanks guys


Range("A69&quot.Select
With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=A18"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "You cannot change this cell"
.InputMessage = ""
.ErrorMessage = _
"To protect the integrity of Station Lists, Stations can only be changed between A15 and A50." & Chr(10) & "" & Chr(10) & "Any changes made will affect all other brands." & Chr(10) & "" & Chr(10) & "Luc"
.ShowInput = True
.ShowError = True
End With

 

[RobBroekhuis]

You could use

Formula1:="=" & selection.offset(-61,0).address

but are you sure you're not better off using the protection features of Excel?

Rob

 

[hopelessliar]

This sheet used to use protection features but I got fed up of users asking me why they couldn't type in it so I wnated to use an instructive error message.

I would imagine I would use the formaula you suggest like this:

Dim myRange as Range
For Each myRange in Range("A19:A25&quot
myRange.Select
With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & selection.offset(-61,0).address


.....>>
next myRange



Am I anywhere close?!

 

[RobBroekhuis]

Exactly. Actually, I prefer not to use the selection object (it gets a little messy, and is hardly ever necessary) - you could simply use:

Dim myRange as Range
For Each myRange in Range("A19:A25&quot
With myRange.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & myRange.offset(-61,0).address

.....>>
next myRange

Rob