Change sheet tab color depending on CountA function result

0 votes

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.

Mar 28, 2023 in Others by narikkadan
• 63,720 points
217 views

1 answer to this question.

0 votes

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
answered Mar 28, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

How to change the background color of AppBar in Flutter?

Hi@akhtar, You can add backgroundColor keyword in your ...READ MORE

answered Aug 12, 2020 in Others by MD
• 95,440 points
1,913 views
0 votes
1 answer

Rounded corners on iOS 13 page sheet

You can alter the view controller in ...READ MORE

answered Sep 20, 2022 in Others by Aditya
• 7,680 points
740 views
0 votes
1 answer

Rounded corners on iOS 13 page sheet

In your view controller, you can change ...READ MORE

answered Sep 21, 2022 in Others by rajatha
• 7,680 points
835 views
0 votes
1 answer
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
996 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,362 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
669 views
0 votes
1 answer

Loop through each cell in a given range and change the value depending on value in a column in the same row

Use match() and if() without code at ...READ MORE

answered Mar 26, 2023 in Others by Kithuzzz
• 38,010 points
686 views
0 votes
1 answer

Run macro when on Worksheet change (i.e. changing sheets, not data within sheet)

Try this: 'put this sub in ThisWorkbook module ...READ MORE

answered Mar 31, 2023 in Others by Kithuzzz
• 38,010 points
479 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP