How to change tab color based on cell value

[rking1234]

First, thank you very much for taking the time to review my question. I have been researching this topic for days, and can't seem to find the correct solution.

I have been trying to figure out how to change the color of a specific tab based on information being entered in cell "B9" for each sheet that the tabs represent. If there is information in cell B9 on "tab 1", I want only tab 1 to change a color. If there is no information entered in cell B9 of tab 2, then I want tab 2 to stay with its default color.

There are 34 tabs I want this to happen to, out of just more than 60 total tabs on the worksheet.

I am new to visual basic, and I am really hoping someone may be willing to help me out. Thank you very much for your time, and have a great day.

 

[VoG]

Maybe like this. Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B9").Value <> 0 Then
    Me.Tab.ColorIndex = 3
Else
    Me.Tab.ColorIndex = xlColorIndexNone
End If
End Sub

Then try entering something in B9 then clearing it.

 

[rking1234]

VoG,

Wow, I never thought I would be so excited by excel. Works perfect, thank you very much. This simple thing is going to help our company very much while reviewing data.

Regards,

Riley

 

[Dezertdog]

Just found this thread, Too easy!
Thank you, VoG.

Scott

 

[Francesca_0208]

Sorry to bring this back up but I thought rather than start a new question, I right clicked on the sheet and pasted the code to test it but it wouldn't work, is there something else that needs to be in the code?

Sorry I'm completely useless with code

 

[VoG]

Macros need to be enabled, obviously.

 

[BullseyeThor]

How would this work if you wanted to have text instead of numbers in the specified cell?

 

[VoG]

How would this work if you wanted to have text instead of numbers in the specified cell?

Maybe this - should work with text or numbers

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B9").Value <> "" Then
    Me.Tab.ColorIndex = 3
Else
    Me.Tab.ColorIndex = xlColorIndexNone
End If
End Sub

 

[BullseyeThor]

Where am I going wrong with this code, I altered it slightly to have it work on text but for some reason can't add for a second text option. Please help. Thank you

 Private Sub Worksheet_Change(ByVal Target As Range) If Range("H11").Value = "Order Sent" Then     Me.Tab.Color = RGB(255, 192, 0) If Range("H11").Value = "Order Confirmed" Then     Me.Tab.Color = RGB(16, 124, 16) Else     Me.Tab.Color = RGB(212, 46, 18) End If End Sub

 

[VoG]

Maybe

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("H11").Value = "Order Sent" Then
    Me.Tab.Color = RGB(255, 192, 0)
ElseIf Range("H11").Value = "Order Confirmed" Then
    Me.Tab.Color = RGB(16, 124, 16)
Else
    Me.Tab.Color = RGB(212, 46, 18)
End If
End Sub