Excel/VBA Rows and column if statement

Question

i'd like to make some function which allow me check if any of field in row 149 is text "Mandatory" then if it's, check if rows below are empty if is then do sth. So I tried sth like this:

If ws.Rows("149") = "Mandatory" Then
If ws.Range("C" & chk.TopLeftCell.Row).Value

But I don't have idea how to write second to check value in each column

Help guys! Thanks!

My vba script for now:

Sub CheckBoxDate()
Dim ws As Worksheet
Dim chk As CheckBox
Dim lColD As Long
Dim lColChk As Long
Dim lRow As Long

Dim rngD As Range
lColD = 0 'number of columns to the right for date

Set ws = Sheets("MA Template_VBack-End")
Set chk = ws.CheckBoxes(Application.Caller)
lRow = chk.TopLeftCell.Row
lColChk = chk.TopLeftCell.Column
Set rngD = ws.Cells(lRow, lColChk + lColD)


Select Case chk.Value
   Case 1   'box is checked
  For Each chk In ws.CheckBoxes

    If ws.Range("C" & chk.TopLeftCell.Row).Value = vbNullString Then

 chk.Enabled = False
        rngD.EntireRow.Interior.Color = vbGreen

    End If
Next chk

   Case Else   'box is not checked
      rngD.ClearContents
      rngD.EntireRow.Interior.ColorIndex = xlColorIndexNone
End Select

End Sub

Now, one of the main problem is how to write this statement:

If ws.Range("C" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("D" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("E" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("f" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("g" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("i" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("t" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("u" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("z" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("ab" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("ac" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("ap" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("at" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("bs" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("bt" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("bu" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("bv" & chk.TopLeftCell.Row).Value = vbNullSt
ring Or ws.Range("bx" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("bz" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("ca" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("cc" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("cd" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("ce" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("ci" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("ck" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("cl" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("cm" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("cn" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("co" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("cp" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("cq" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("cs" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("ea" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("ed" & chk.TopLeftCell.Row).Va
lue = vbNullString Or ws.Range("ee" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("eg" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("eh" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("ei" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("ej" & chk.TopLeftCell.Row).Value = vbNullString Then

Because this will be based on first if

Answer 1

To find if "Mandatory" appears in row 149 in ws worksheet, use the Application.Match function.

see code below:

If Not IsError(Application.Match("Mandatory", ws.Rows(149), 0)) Then ' <-- successful match
    ' rest of your code goes here

End If

Answer 2

1. Determine the range to be checked

It is better practice to specify exactly the range, you want to check for values. Otherwise, you can make your VBA inefficient. So maybe you only need to check range ("A149:Z149"), from now on reffered to as SearchRange

2. Get the range into VBA array (optional, recommended)

In VBA you can transfer the SearchedRange to Variant array. Like so:

Dim SearchedArray as Variant
SearchedArray  = sheetXY.range("A149:Z149").value2

3. Check each member of array against criteria

You can then loop through all the values in Variant array. Like so:

Dim Member as Variant
For Each Member In SearchedArray
 If Member = "Mandatory" Then
  Msgbox "Found it!"
  Exit For
 End If
Next