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,420 points
147 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,816 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
594 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,640 points
628 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
875 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,181 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
479 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
487 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
346 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