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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,298
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

Well-known Member
Joined
Feb 23, 2021
Messages
2,298
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

Well-known Member
Joined
Feb 23, 2021
Messages
2,298
Office Version
  1. 365
Platform
  1. Windows
No problem. Glad I could help and thank you for letting me know/
 

Forum statistics

Threads
1,148,334
Messages
5,746,162
Members
423,997
Latest member
moofish87

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