Excel/VBA Rows and column if statement -

i'd make function allow me check if of field in row 149 text "mandatory" if it's, check if rows below empty if sth. tried sth this:

if ws.rows("149") = "mandatory" if ws.range("c" & chk.topleftcell.row).value 

but don't have idea how write second check value in each column

help guys! thanks!

my vba script now:

sub checkboxdate() dim ws worksheet dim chk checkbox dim lcold long dim lcolchk long dim lrow long  dim rngd range lcold = 0 'number of columns right 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 checked   each chk in ws.checkboxes      if ws.range("c" & chk.topleftcell.row).value = vbnullstring   chk.enabled = false         rngd.entirerow.interior.color = vbgreen      end if next chk     case else   'box not checked       rngd.clearcontents       rngd.entirerow.interior.colorindex = xlcolorindexnone end select  end sub 

now, 1 of main problem how write 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 

because based on first if

to find if "mandatory" appears in row 149 in ws worksheet, use application.match function.

see code below:

if not iserror(application.match("mandatory", ws.rows(149), 0)) ' <-- successful match     ' rest of code goes here  end if