Forum Discussion
limiting check for blanks for some columns only
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
On Error GoTo OutOfRange
If Target.Row = 2 And Target.Offset(1) = "" Then Exit Sub
For Each Cell In Intersect(Target, Columns("A:J"))
If Application.CountIf(Range(Cells(2, Cell.Column), Cell.Offset(-1)), "") Or (Cell.Value = "" And Cell.Offset(1).Value <> "") Then
MsgBox "You cannot enter a value in a column if one of the cells above it entry cell is blank nor can you delete the contents of a cell if there is a non-blank cell below it!"
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit For
End If Next
OutOfRange:
End Sub
The function of the above sub is vividly expressed in the MsgBox.
It was an answer to my query, it worked just fine for Columns A to J, but now I would like it to limit its checking for columns ABCDGH only - meaning the rest of the columns (EFIJ) are "free entry columns" (no checking is required, they can have any blanks within their columns).
many thanks
2 Replies
For Each Cell In Intersect(Target, Columns("A:J"))
I tried changing the above to:
For Each Cell In Intersect(Target, Columns("A:D,G:H"))
not only did it not work, the whole sub just stop working!
...---... (sos)!
many many thanks
- Mr Logit provided the correct script just for that..
For Each Cell In Intersect(Target, Columns("A:D,G:H"))
change the Columns into Range and it will do the trick:
For Each Cell In Intersect(Target, Range("A:D,G:H"))
hope someone might need this too...
