title: Change worksheet tab color if range of cells contains text
body: <p>I have tried code that I've found here on stackoverflow, and elsewhere but they aren't working as I think they can. I'll list them below. I'm almost certain this is an easy question.</p>
  
  <p><strong>What I'm trying to do:</strong> If in any of the cells in the range A2:A100 there is any text or number whatsoever, then make the worksheet tab red. And I will need to do this on over 20 tabs. This must execute upon opening the workbook, and thus not require manually changing a cell or recalculating.</p>
  
  <p><strong>The problems I've had with other code:</strong> As far as I can tell they require editing a cell, and then quickly hitting enter again. I tried SHIFT + F9 to recalculate, but this had no effect, as I think this is only for formulas. Code 1 seems to work albeit with having to manually re-enter text, but no matter what color value, I always get a black tab color.</p>
  
  <p><strong>Code I've tried:</strong></p>
  
  <p><em>Code 1:</em></p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
      MyVal = Range("A2:A27").Text
  
      With ActiveSheet.Tab
          Select Case MyVal
              Case ""
                  .Color = xlColorIndexNone
              Case Else
                  .ColorIndex = 6
          End Select
      End With
  End Sub
  </code></pre>
  
  <p><em>Code 2:</em> This is from a <a href="https://stackoverflow.com/questions/21733897/vba-how-to-change-tab-color-across-multiple-tabs-based-on-formula-cell-value">stackoverflow</a> question, although I modified the code slightly to fit my needs. Specifically, if in the set range there are no values to leave the tab color alone, and otherwise to change it to color value 6. But I'm sure I've done something wrong, I'm unfamiliar with VBA coding.</p>
  
  <pre><code>Private Sub Worksheet_Calculate()
      If Range("A2:A100").Text = "" Then
          ActiveWorkbook.ActiveSheet.Tab.Color = xlColorIndexNone
      Else
          ActiveWorkbook.ActiveSheet.Tab.Color = 6
      End If
  End Sub
  </code></pre>
  
  <p>Thanks for your help!</p>
  
  <p><em>I posted this on <a href="https://superuser.com/questions/900823/how-to-give-worksheet-tab-a-color-if-range-of-cells-contains-text">superuser</a> first, but perhaps stackoverflow is more appropriate since it is explicitly programming-related.</em></p>