How to give worksheet tab a color if range of cells contains text

Question

I have tried code that I've found on SE, 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.

What I'm trying to do: If in any of the cells in the range A2:A100 there is any text or number whatsoever, then make the worksheet tab yellow. And I will need to do this on over 20 tabs.

The problems I've had with other code: 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.

Code I've tried:

Code 1:

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 2: This is from a stackoverflow 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.

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

Thanks for your help!

Answer 1

On both cases you use range.text which is a string variable, it gives you only the text in the first cell in your range, you need a bit more complex code. If i understand well you just want to test whether you cross are not blank and not interested in the content. Here are some tips to set your criteria:

• use worksheetfunction to get number of non-empty cells: worksheetfunction.counta("A2:A100")>0

• or go to the next non-empty cell in your range using end() method.

Answer 2

You may need to use the following event:

Private Sub Worksheet_Change(ByVal Target As Range)

Instead of:

Worksheet_Calculate()

If you have the code in Worksheet_Calculate, it might not execute if the worksheet doesn't need to be recalculated (i.e. there are no cells with formulas in the worksheet)

---

Paste this into the code window of the sheets that you need to set this up for:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myRange As Range
    Set myRange = ActiveSheet.Range("A2:A100")
    If Application.WorksheetFunction.CountBlank(myRange) = 99 Then
        ActiveWorkbook.ActiveSheet.Tab.Color = xlColorIndexNone
    Else
        ActiveWorkbook.ActiveSheet.Tab.Color = vbRed
    End If
End Sub

This uses the COUNTBLANK function which is described here.

You may also use the code below:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myRange As Range
    Set myRange = ActiveSheet.Range("A2:A100")
    If Application.WorksheetFunction.CountA(myRange) = 0 Then
        ActiveWorkbook.ActiveSheet.Tab.Color = xlColorIndexNone
    Else
        ActiveWorkbook.ActiveSheet.Tab.Color = vbRed
    End If
End Sub

This uses the COUNTA function described here

---

The COUNTBLANK and the COUNTA formulas might seem to the the opposite of each other. However there are 2 differences I have noticed:

• COUNTBLANK counts a cells that have a formula, that doesn't return any value as blank. COUNTA would count such a cell as not being blank. A simple example of such a formula would be: =IF(1=1,"","test"). This formula evaluates so that it doesn't return any value.

• COUNTA supports non contiguous ranges of cells. COUNTBLANK doesn't.

---

To have the tab colors updated when the workbook is open:

You can have the same code above inside BOTH the Worksheet_Change and Worksheet_Calculate function of each worksheet code section.

OR you can add the following to the workbook code window:

Private Sub Workbook_Open()
    Call Sheet1.Worksheet_Change(ActiveSheet.Range("A1"))
    Call Sheet2.Worksheet_Change(ActiveSheet.Range("A1"))
    'Add lines of code for each sheet that you need the tab colors updated for. If you need tab colors updated for ALL sheets, you can loop through the sheets and call `Worksheet_Change` as well.
End Sub