Changing worksheet tab colour by cell value

Ld30

New Member
Joined
Sep 14, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have a spreadsheet going with several tabs and each tab represents 1 week. In each tab, I have a drop down column with 2 options: booked and tentative (in a given week, we can only book 5 events max). I currently have it set so that if it is tentative, the cell becomes red and if it is booked, the cell becomes green. I would also like to have the tab colour change with the following parameters: if all 5 dates are booked, the tab becomes green. If any of the 5 dates are tentative, the tab becomes red. Wondering if anyone can assist?
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
4,990
Office Version
  1. 365
Platform
  1. Windows
Try the below:
In the developer window double click on the relevant sheet in the project window on the left.
It should open a code window on the right.
Copy the code below into that window.

Since it is really hard to see the colour when the sheet is active I have opted to change the colour when you click off the sheet.
This will reduce how often the code has to run.

You will need to change the range to fit where you have your tentative / booked entries.

VBA Code:
Option Explicit

Private Sub Worksheet_Deactivate()
    
    Dim cntBooked As Long
    Dim cntTentative As Long
    Dim rngStatus As Range
    
    Set rngStatus = Me.Range("B4:B8")  ' Change to range recording booked and tentative
    
    cntBooked = Application.CountIfs(rngStatus, "booked")
    cntTentative = Application.CountIfs(rngStatus, "tentative")
    
    If cntBooked = 5 Then
        With Me.Tab
            .Color = 255
        End With
    
    ElseIf cntTentative = 5 Then
        With Me.Tab
            .Color = 5296274
        End With
    Else
        With Me.Tab
            .Color = False
        End With
    End If

End Sub
 

Ld30

New Member
Joined
Sep 14, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Thank
Try the below:
In the developer window double click on the relevant sheet in the project window on the left.
It should open a code window on the right.
Copy the code below into that window.

Since it is really hard to see the colour when the sheet is active I have opted to change the colour when you click off the sheet.
This will reduce how often the code has to run.

You will need to change the range to fit where you have your tentative / booked entries.

VBA Code:
Option Explicit

Private Sub Worksheet_Deactivate()
   
    Dim cntBooked As Long
    Dim cntTentative As Long
    Dim rngStatus As Range
   
    Set rngStatus = Me.Range("B4:B8")  ' Change to range recording booked and tentative
   
    cntBooked = Application.CountIfs(rngStatus, "booked")
    cntTentative = Application.CountIfs(rngStatus, "tentative")
   
    If cntBooked = 5 Then
        With Me.Tab
            .Color = 255
        End With
   
    ElseIf cntTentative = 5 Then
        With Me.Tab
            .Color = 5296274
        End With
    Else
        With Me.Tab
            .Color = False
        End With
    End If

End Sub
Thank you so much for your response! It did work (just had to switch the colours around). The only other thing is that if there were any tentative selections at all (even just 1), we would want the tab to turn red. Is there a workaround for that?
 

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
4,990
Office Version
  1. 365
Platform
  1. Windows
if there were any tentative selections at all (even just 1), we would want the tab to turn red. Is there a workaround for that?
Just swap the if statement order around and make it >= 1 for tentative.
Here is the revised version.

VBA Code:
Private Sub Worksheet_Deactivate()
    
    Dim cntBooked As Long
    Dim cntTentative As Long
    Dim rngStatus As Range
    
    Set rngStatus = Me.Range("B4:B8")  ' Change to range recording booked and tentative
    
    cntBooked = Application.CountIfs(rngStatus, "booked")
    cntTentative = Application.CountIfs(rngStatus, "tentative")
    
    If cntTentative >= 1 Then
        With Me.Tab
            .Color = 255
        End With
    
    ElseIf cntBooked = 5 Then
        With Me.Tab
            .Color = 5296274
        End With
    Else
        With Me.Tab
            .Color = False
        End With
    End If

End Sub
 
Solution

Ld30

New Member
Joined
Sep 14, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Thank

Thank you so much for your response! It did work (just had to switch the colours around). The only other thing is that if there were any tentative selections at all (even just 1), we would want the tab to turn red. Is there a workaround for that?
Oh, haha I just figured it out. I removed the "=5" from this line "ElseIf cntTentative = 5 Then"

Thank you so much!
 

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
4,990
Office Version
  1. 365
Platform
  1. Windows
No problem. Glad I could help and thank you for letting me know/
 

Forum statistics

Threads
1,175,479
Messages
5,897,675
Members
434,669
Latest member
ykhan344

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top