syntax needed to validate cells on spreadsheet
 

I am trying to write some code to test the following.
If column C contains a value then check that columns n to k also contain a
value. This check will apply to a number of rows on the activesheet. It is
possible that column c may not contain any values at all but I need a test to
ensure that if there are, then columns n to k are not empty. Would a Do
Until Not IsEmpty check on column c be more efficient in place of a For Each
type statement to avoid checking each row? However, I need to ensure that
the code does not get stuck in a continuous loop.
In summary the code needs to skip any empty cells in column c but if it
finds a value then check columns n to k are populated with a value. If these
columns contain a value on the same row as the test in column c then skip
else I need a message box warning the user that they've entered a value in
column c but not completed their input in columns n to k. Does that make
sense?
Can anyone kindly help please?
Many thanks
Jacqui

syntax needed to validate cells on spreadsheet
 

I think I'd use a formula in an adjacent cell (maybe insert a new column D and
have all things shift to the right):

=IF(C1="","",IF(COUNTA(L1:O1)=4,"","Please complete L:O!"))

Format that cell in big bold red letters.

========

In code:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim wks As Worksheet
Dim myRngToCheck As Range

Set wks = Worksheets("sheet1")

With wks
Set myRng = .Range("C1", .Cells(.Rows.Count, "C").End(xlUp))

For Each myCell In myRng.Cells
If myCell.Value = "" Then
'do nothing
Else
Set myRngToCheck = .Cells(myCell.Row, "K").Resize(1, 4)
If Application.CountA(myRngToCheck) _
< myRngToCheck.Cells.Count Then
MsgBox "Please fix Row: " & myCell.Row
End If
End If
Next myCell
End With
End Sub

Jacqui wrote:

I am trying to write some code to test the following.
If column C contains a value then check that columns n to k also contain a
value. This check will apply to a number of rows on the activesheet. It is
possible that column c may not contain any values at all but I need a test to
ensure that if there are, then columns n to k are not empty. Would a Do
Until Not IsEmpty check on column c be more efficient in place of a For Each
type statement to avoid checking each row? However, I need to ensure that
the code does not get stuck in a continuous loop.
In summary the code needs to skip any empty cells in column c but if it
finds a value then check columns n to k are populated with a value. If these
columns contain a value on the same row as the test in column c then skip
else I need a message box warning the user that they've entered a value in
column c but not completed their input in columns n to k. Does that make
sense?
Can anyone kindly help please?
Many thanks
Jacqui

--

Dave Peterson