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.

 

[rick111]

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

HI Try This

If Range("H11").Value = "Order Sent" Then
With ActiveSheet.Tab
.Color = RGB(255, 192, 0)
.TintAndShade = 0
End With
ElseIf Range("H11").Value = "Order Confirmed" Then
With ActiveSheet.Tab
.Color = RGB(16, 124, 16)
.TintAndShade = 0
End With
Else
With ActiveSheet.Tab
.Color = RGB(212, 46, 18)
.TintAndShade = 0
End With

End If
End Sub

 

[punktilend]

Is there a way to have,

[COLOR=#333333]If Range("H11").Value = "Order Sent" Then[/COLOR]

Use the entire H column?

 

[cvincent]

I was able to set the Private Sub Macro up to change the tab color to blue when my cell value (cell C54) changes to "HOLD". I only want it to change to blue when an account is first placed on "HOLD". Then when the account is removed from "HOLD" to another status, I want the tab to go back to default color. How can I do this?

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("C54").Value = "" Then
Me.Tab.ColorIndex = -4142 ' No Color
Else
Me.Tab.ColorIndex = 23 ' Blue
End If
End Sub

 

[Morbia]

i was also trying to figure out how to change the tab color based on value
i wanted it to be red for negative, yellow for neutral, and green for positive based the cell J7

 

[DDRA Steampunk]

Hi Help is deeply appreciated! I have a similar question. I've put what I think are the relevant cells / formulas here. I want the workbook to automatically change the tab colors based on the Quarter extracted from the Tab Name when the date on the tab gets altered, or when the workbook is opened, or both. I want the tabs to be colored 4 different colors to match the 4 colors on the YTD Summary page. So far I can get the above code to work when hard setting the Quarter, but not from one calculated.

The tab name is formatted as "YYYY-MM-DD Event Name". We use a Template Page that gets copied for each day of every event (about 100 sheets per year). I want the template, indexes, and summary pages color to remain unchanged (they are already color coded) and only color the Event pages as they get added. All of the summaries/indexes have specific Page Names, the event pages are just called Sheet1, etc.

C1 extracts the date from the tab name: =(MID(CELL("Filename",W1),SEARCH("]",CELL("Filename",W1),1)+1,10))

H1 displays "Quarter:" but contains the formula to calculate the quarter: =ROUNDUP(MONTH(C1)/3,0)

I1 displays the quarter name: =IF($H$1=1,"1st",IF($H$1=2,"2nd",IF($H$1=3,"3rd",IF($H$1=4,"4th","ERROR"))))

 

[Angelo Leon]

Hello, using this code:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice

If Range("E63").Value <> 0 Then
Me.Tab.ColorIndex = 3
Else
Me.Tab.ColorIndex = 4

End If
End Sub

Where cell E63 has a formula and that number changes <> 0, I get a user-defined type not defined but the tab still changes color. Please advise.