title: Loop through spreadsheet and find empty/blank cells that meet a condition
body: <p>VBA code should loop through spreadsheet and highlight the cells within a range that meet a condition. For example: for each employee row at least 1 of 7 columns must have a value. The required columns/range are separated. Starting at column U, W, Z, AB, AG, AJ etc. within my VBA code each column is set as a select case, and upon clicking a button to validate it loops through. I have tried rngoffset and calling from a function and I'm still stuck. The only way is to call under each Case. Is there a better way? If needed I can provide some sample code.</p>
  <p>Thanks</p>
  <p>Sample code:</p>
  <pre><code>Case Band2Bonus:
  </code></pre>
  <p>‘If column is not hidden, then perform validation
  On Error GoTo ErrHandler
  If ws.Column(intcol).Hidden=False Then
  ‘clear array</p>
  <pre><code>Set quickArray= Nothing
  Set rngOffset1= Nothing
  Set rngOffset2= Nothing
  Set rngOffset3= Nothing
  Set rngOffset4= Nothing
  Set rngOffset5= Nothing
  Set rngOffset6= Nothing
  Set rngOffset7= Nothing
  str=””
  </code></pre>
  <p>‘Quick-fill array using variant</p>
  <pre><code>quickArray= ws.Cells(lngHeader+1,intCol).Resize(lngLastRow)
  For lngRow=lngHeader + 1 To lngLastRow
  
  Set rngOffset1= ws.Cells(lngRow Band2Bonus)
  Set rngOffset2= ws.Cells(lngRow Band3Bonus)
  Set rngOffset3= ws.Cells(lngRow Band4Bonus)
  Set rngOffset4= ws.Cells(lngRow Step1Band)
  Set rngOffset5= ws.Cells(lngRow Step2Band)
  Set rngOffset6= ws.Cells(lngRow Step3Band)
  Set rngOffset7= ws.Cells(lngRow Step4Band)
  
  If CBool ((rngOffset1.Value = “” And rngOffset2.Value = “” And _ 
             rngOffset3.Value = “” And rngOffset4.Value = “” And _ 
             rngOffset5.Value = “” And rngOffset6.Value = “” And _
             rngOffset7.Value = “”)) Or _
  Not ValidBand(quickArray((lngRow-lngHeader), 1)) Then 
     lngErrorCount=lngErrorCount +1
      ws.Cells(lngRow,intcol).Interior.colorIndex=failedvalidation
  Else 
  ws.Cells(lngRow,intcol).Value= UCase(ws.Cells(lngRow,intCol).Value)
  
  End If
     Next lngRow
  End If
  </code></pre>
  <p>Case SetPlan:
  ‘conditional field based off of set up menu selection- only added to view order of sheet</p>
  <p>Case Band3Bonus:
  ‘If column is not hidden, then perform validation
  On Error GoTo ErrHandler
  If ws.Column(intcol).Hidden=False Then
  ‘clear array
  Set quickArray= Nothing
  Set rngOffset1= Nothing</p>
  <p>'etc.... code will continue run through each case. I was just hoping there was a better way to run through this range</p>