Change sheet tab color depending on CountA function result

Question

I have a document that is on multiple sheets. Every workbook shows the company's projects and how many hours each employee put into them.

There are about 25 tabs, and I need to indicate with a different colour whether a cell is empty or not.

I'm using the next macro, and it works. Nevertheless, depending on the document I'm seeing, it will alter EVERY TAB COLOR. It doesn't alter the hue on its own. Do you know why this occurs?

Option Explicit

Sub ColorTabs()
    Dim ws As Worksheet
    Dim CountaRange As Range
    
    Set CountaRange = Range("B7:H26")
    
    For Each ws In ThisWorkbook.Worksheets
        If Application.WorksheetFunction.CountA(CountaRange) = 0 Then
           ws.Tab.Color = vbRed
        Else
           ws.Tab.Color = vbBlue
           
        End If
    Next ws
End Sub

I am expecting to color every tab independently. Not all tabs at once.

Answer 1

Try using the ws. prefix to set your range inside of your loop. Your range is now predetermined by your ActiveSheet and does not alter for each tab.

Option Explicit

Sub ColorTabs()
    Dim ws As Worksheet
    Dim CountaRange As Range
    
    For Each ws In ThisWorkbook.Worksheets
        Set CountaRange = ws.Range("B7:H26")
        If Application.WorksheetFunction.CountA(CountaRange) = 0 Then
           ws.Tab.Color = vbRed
        Else
           ws.Tab.Color = vbBlue
           
        End If
    Next ws
End Sub