Sequencial validations in adjacent cells


New Member
Feb 24, 2012
Hello all,

Below please find the current code I am using to validate my values.
Right now I have that upon entering a value in a cell it validates if it is between a certain range given by values in two cells, display a message and regardless if data valid or not locks the cell.

What I would like to do is if it is out of the given range, displays the message and unlocks the adjacent cell to the right.
User enters value in this newly unlocked cell and validates it again within this range, lock the cell. if our of range, unlocks the adjacent cell to the right and validates once again, lock the cell.

initial value is entered in column C, lock cell in C, if out of range, unlock D. Validate value enterd in D, lock cell in D, if out of range display error message and unlock E. Validate value entered in E, lock cell in E, if out of range display message.

I am starting with a locked sheet and Here is the code I have in my worksheet.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo justenditall
Application.EnableEvents = False
If Not Intersect(Target, Range("C:C")) Is Nothing Then
If Target.Value <> "" Then ActiveSheet.Unprotect Password:="password"
Target.Cells.Locked = True
Target.Offset(0, -1).Value = Now
ActiveSheet.Protect Password:="password"
If Target.Value < Range("$D$5") Or Target.Value > Range("$F$5") Then MsgBox "Input out of range! Another Reading Required."
Else: End If
ActiveSheet.Protect Password:="password"
justenditall: Application.EnableEvents = True
End Sub

Thank you very very mcuh for all your help

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics