title: VBA to clear contents of cell if range is empty
body: <p>I have a code that works pretty well except for a flaw. It checks for the text in a range and returns a text in the adjacent cell. I want it return a blank cell (<strong>Column B</strong>) if the adjacent range rows (<strong>Column C to G</strong>) are empty. This is the code below</p>
  
  <pre><code>Sub status_summary()
      Dim r As Integer
      Dim fail As Boolean
      Dim mrit As Boolean
      Dim pass As Boolean
  
      For r = 2 To 6
          If Range("C" &amp; r) = "Failed" Then fail = True
          If Range("C" &amp; r) = "Merit" Then mrit = True
          If Range("C" &amp; r) = "Pass" Then pass = True
  
          If Range("D" &amp; r) = "Failed" Then fail = True
          If Range("D" &amp; r) = "Merit" Then mrit = True
          If Range("D" &amp; r) = "Pass" Then pass = True
  
          If Range("E" &amp; r) = "Failed" Then fail = True
          If Range("E" &amp; r) = "Merit" Then mrit = True
          If Range("E" &amp; r) = "Pass" Then pass = True
  
          If Range("F" &amp; r) = "Failed" Then fail = True
          If Range("F" &amp; r) = "Merit" Then mrit = True
          If Range("F" &amp; r) = "Pass" Then pass = True
  
          If Range("G" &amp; r) = "Failed" Then fail = True
          If Range("G" &amp; r) = "Merit" Then mrit = True
          If Range("G" &amp; r) = "Pass" Then pass = True
  
          If pass = True Then Range("B" &amp; r) = "Pass"
          If mrit = True Then Range("B" &amp; r) = "Merit"
          If fail = True Then Range("B" &amp; r) = "Failed"
  
          fail = False
          mrit = False
          pass = False
  
      Next r
  
      MsgBox ("All Done")
  End Sub
  </code></pre>