Check if a range has data in condition to a cell

[girapas]

In a sheet a user enters data in a range, say A1 to F5. In column A the user selects an item from a named list (via Data\Validation\List) with text values. Then, in columns B to F the user enters numbers. In any row he may enters 1,2,3,4,5 numbers or none. When he finishes he clicks a button with name 'Confirm' that saves the data and makes some calculations.
What I want is a macro to run after button click that:
1) If the user selects a value in A and doesn't enter number in any of the cells that are in the same row (I mean if he leaves a row only with data in A, without at least one number in adjacent cells of columns B to F), a stop explaining message to be appeared and prompt him to correct it.
2) If the user enters number or numbers in any cell of the range B1 to F5 and omits to select an item in A, again a stop message to be appeared.

Notice that in all the cells there's Conditional Formatting (fonts, patterns etc.) with all 3 conditions (Excel 2003).

Thanks in advance...

 

[Ruddles]

See if this works for you:-

Option Explicit
 
Sub CheckRange()
 
  Dim dRange As Range
  Dim sRow As Long
  Dim eRow As Long
  Dim sCol As Long
  Dim eCol As Long
 
  Dim iPtr As Long
  Dim iCell As Long
  Dim bEmpty As Boolean
  Dim bWarning As Boolean
  Dim iErrors As Long
  Dim sMessage As String
 
  Set dRange = Range("[COLOR=red][B]A1:F5[/B][/COLOR]")
  sRow = dRange.Row
  eRow = sRow + dRange.Rows.Count - 1
  sCol = dRange.Column
  eCol = sCol + dRange.Columns.Count - 1
 
  bWarning = False
  sMessage = ""
  iErrors = 0
  For iPtr = sRow To eRow
    bEmpty = True
    For iCell = sCol + 1 To eCol
      If Not IsEmpty(Cells(iPtr, iCell)) Then
        bEmpty = False
        Exit For
      End If
    Next iCell
    Select Case True
      Case IsEmpty(Cells(iPtr, sCol)) And Not bEmpty
        bWarning = True
        iErrors = iErrors + 1
        sMessage = sMessage & ", " & CStr(iPtr)
      Case Not IsEmpty(Cells(iPtr, sCol)) And bEmpty
        bWarning = True
        iErrors = iErrors + 1
        sMessage = sMessage & ", " & CStr(iPtr)
    End Select
  Next iPtr
 
  If bWarning = True Then
    MsgBox "Error" & IIf(iErrors > 1, "s", "") _
       & " detected in row" & IIf(iErrors > 1, "s", "") _
       & ": " & Mid(sMessage, 3) & Space(10), vbOKOnly + vbExclamation
  Else
    MsgBox "No errors detected" & Space(10), vbOKOnly + vbInformation
  End If
 
End Sub

Just change the bit in red to suit.

 

[girapas]

Great! It works fine. Thank you very much...