Tab Color Change Based on Value

[r0bism123]

Hi All,

I need a bit of help here. I am trying to utilize the word "Vacant" to trigger a tab color change on my workbook based on a value entered on another sheet. The code works as intended when I enter "Vacant" in cell $DB$7 on the same sheet but, when that cell is linked to another sheet, the code no longer changes the tab color.

Any thoughts? Thanks in advance for your help.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$DB$7" Then
        Select Case Target.Text
        Case "Vacant"
            Me.Tab.Color = RGB(255, 76, 76)
        Case "vacant"
            Me.Tab.Color = RGB(255, 76, 76)
        Case Else
            Me.Tab.Color = RGB(255, 248, 66)
        End Select
    End If
End Sub

 

[My Aswer Is This]

Try something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$DB$7" Then
        Select Case Target.Text
        Case "Vacant"
            Sheets(1).Tab.Color = RGB(255, 76, 76)
        Case "vacant"
            Sheets(3).Tab.Color = RGB(255, 76, 76)
        Case Else
            Sheets("George").Tab.Color = RGB(255, 248, 66)
        End Select
    End If
End Sub

 

[Peter_SSs]

.. but, when that cell is linked to another sheet, the code no longer changes the tab color.

That sounds to me like you are saying that DB7 contains a formula. If that is so, a Worksheet_Change event will not be triggered by that formula recalculating to a different value.

You could instead use a Worksheet_Calculate event instead ...

Private Sub Worksheet_Calculate()
  Select Case LCase(Range("DB7").Value)
    Case "vacant"
      Me.Tab.Color = RGB(255, 76, 76)
    Case Else
      Me.Tab.Color = RGB(255, 248, 66)
  End Select
End Sub

.. however, I think calculate event code is best avoided if there is a suitable alternative as this code will most likely be running a lot more than is required.

Instead, it would most likely be possible to achieve your desired result by using a Worksheet_Change event code on the other sheet that is providing the input to the DB7 formula - provided the cell(s) in that sheet that feed into your DB7 formula are themselves manually input or input via code. If you want to investigate that further, can you provide the following details.

1. What is the name of the sheet that is to have its tab colour changed and contains the DB7 formula?

2. What is the exact formula in DB7?

There will probably be more questions once those things are known but I'm not sure exactly what to ask until I have seen those answers - especially Q2.

 

[r0bism123]

Good morning Peter,

Thank you for taking the time to explore this fix - I really appreciate it.

Yes, you are correct. There is a formula in cell DB7. The formula is: ='Rent Schedule'!A16. The sheet is currently named "Suite1" but that will change based on the occupant of the suite which turns over often and will never be the same.

Also, I use the rent schedule sheet to enter whether there is a occupant in the suite or if it is vacant. I want the word "Vacant" and "vacant" entered into cell 'Rent Schedule'!A16 to trigger the color change on another sheet3. I have 68 of them. As such, 'Rent Schedule'!A17 would trigger the color change on sheet4; 'Rent Schedule'!A18 would trigger the color change on sheet5 and so on.

Let me know your thoughts. Again, I really appreciate the help.

Thanks!

 

[r0bism123]

Try something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$DB$7" Then
        Select Case Target.Text
        Case "Vacant"
            Sheets(1).Tab.Color = RGB(255, 76, 76)
        Case "vacant"
            Sheets(3).Tab.Color = RGB(255, 76, 76)
        Case Else
            Sheets("George").Tab.Color = RGB(255, 248, 66)
        End Select
    End If
End Sub

Good morning,

Thank you for your input here. This code works! But for only one sheet.

How do suggest I utilize the same Case Target.Text but on a different Target.Address? For example, I want the word "Vacant" or "vacant" entered into cell 'Rent Schedule'!A16 to trigger the color change on another sheet3. And then 'Rent Schedule'!A17 would trigger the color change on sheet4; 'Rent Schedule'!A18 would trigger the color change on sheet5 and so on.

Thanks again.

 

[My Aswer Is This]

Try this:
Put this script in sheet named

Rent Schedule​

This will work from any cell in column "A" starting in Row 16

So if you enter Vacant or vacant in Range ("A16") the sheet(3) tab color will turn Red
If you enter vacant or Vacant in Range("A17") sheet (4) tab color will turn Reb
And so on
If you enter any value other then Vacant or vacant the tab color turns yellow

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
On Error GoTo M
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Row > 15 Then
If Target.Value = "Vacant" Or Target.Value = "vacant" Then
Sheets(Target.Row - 13).Tab.Color = RGB(255, 76, 76)
Else
Sheets(Target.Row - 13).Tab.Color = RGB(255, 248, 66)
End If
End If
End If
Exit Sub
M:
MsgBox "That sheet number  " & (Target.Row - 13) & "  does not exist"
End Sub

 

[r0bism123]

Thank you so much! This worked perfectly!

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Thank you so much! This worked perfectly!