Change tab color based on text within worksheet

[Jamie Weeks]

I've never used macros before but I've tried a few macros I found online to solve and no success so far. Here's what I'm trying to do:

My workbook contains around 20 sheets (tabs). I will choose one to use for this example:

For the sheet (tab) named "Mirador" I would like the tab color to change automatically based upon the text in cell G9.

If G9 = "GREEN" I want the tab to be green.
If G9 = "YELLOW" I want the tab to be yellow.
If G9 = "RED" I want the tab to be red.
If G9 = "COMPLETE" I want the tab to be blue.
If G9 = "CXLD" I want the tab to be pink.

Cell G9 is a merged cell with G9 being the first cell in the range. I'm not sure if that impacts this.

For clarity, just to make sure I'm doing the right thing. I put the macro text in the window that pops up when I right click the tab and click "view code", correct?

Thank you in advance!
Jamie

 

[JoeMo]

Can you post the code you are using without success?

 

[Eric W]

Welcome to the forum.

You've about got it right:

1) Open your workbook to the Mirador sheet
2) Right click on the sheet tab and select View Code
3) Paste the following code into the window that opens

Private Sub Worksheet_Change(ByVal target As Range)

    If Intersect(target, Range("G9")) Is Nothing Then Exit Sub
    
    Select Case LCase(Range("G9").Value)
        Case "green":    ActiveSheet.Tab.Color = 5287936
        Case "yellow":   ActiveSheet.Tab.Color = 65535
        Case "red":      ActiveSheet.Tab.Color = 255
        Case "complete": ActiveSheet.Tab.Color = 12611584
        Case "cxld":     ActiveSheet.Tab.Color = 16751103
    End Select
            
End Sub

4) Press Alt-Q to exit the VBA editor
5) Try it out!

Let me know how it works.

 

[JoeMo]

Welcome to the forum.

You've about got it right:

1) Open your workbook to the Mirador sheet
2) Right click on the sheet tab and select View Code
3) Paste the following code into the window that opens

Private Sub Worksheet_Change(ByVal target As Range)

    If Intersect(target, Range("G9")) Is Nothing Then Exit Sub
    
    Select Case LCase(Range("G9").Value)
        Case "green": ActiveSheet.Tab.Color = 5287936
        Case "yellow": ActiveSheet.Tab.Color = 65535
        Case "red": ActiveSheet.Tab.Color = 255
        Case "complete": ActiveSheet.Tab.Color = 12611584
        Case "cxld": ActiveSheet.Tab.Color = 16751103
    End Select
            
End Sub

4) Press Alt-Q to exit the VBA editor
5) Try it out!

Let me know how it works.

Might want to put that in a Thisworkbook module. I think the OP wants this to apply to all 20 worksheets.

 

[Eric W]

Might want to put that in a Thisworkbook module. I think the OP wants this to apply to all 20 worksheets.

Good thought.

Jamie, if you want the tab of every sheet to change based on what's in G9 on the given sheet, try this:

1) Open your workbook to any sheet
2) Right click on the sheet tab and select View Code
3) In the Project window on the left, double-click on ThisWorkbook
4) Paste the following code into the window that opens

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Intersect(Target, Sh.Range("G9")) Is Nothing Then Exit Sub
    
    Select Case LCase(Sh.Range("G9").Value)
        Case "green":    Sh.Tab.Color = 5287936
        Case "yellow":   Sh.Tab.Color = 65535
        Case "red":      Sh.Tab.Color = 255
        Case "complete": Sh.Tab.Color = 12611584
        Case "cxld":     Sh.Tab.Color = 16751103
    End Select

End Sub

5) Press Alt-Q to exit the <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> editor

 

[Rhodie72]

I've never used macros before but I've tried a few macros I found online to solve and no success so far. Here's what I'm trying to do:

My workbook contains around 20 sheets (tabs). I will choose one to use for this example:

For the sheet (tab) named "Mirador" I would like the tab color to change automatically based upon the text in cell G9.

If G9 = "GREEN" I want the tab to be green.
If G9 = "YELLOW" I want the tab to be yellow.
If G9 = "RED" I want the tab to be red.
If G9 = "COMPLETE" I want the tab to be blue.
If G9 = "CXLD" I want the tab to be pink.

Cell G9 is a merged cell with G9 being the first cell in the range. I'm not sure if that impacts this.

For clarity, just to make sure I'm doing the right thing. I put the macro text in the window that pops up when I right click the tab and click "view code", correct?

Thank you in advance!
Jamie

Looking at what you have in terms of color codes, consider using the RGB collection names available in the xlrgbcolor:

       Case "green":    ActiveSheet.Tab.Color = vbGreen
        Case "yellow":   ActiveSheet.Tab.Color = vbYellow
        Case "red":      ActiveSheet.Tab.Color = vbRed
        Case "complete": ActiveSheet.Tab.Color = rgbLimeGreen   'was colorindex=12611584/rgb(0,176,80)
        Case "cxld":     ActiveSheet.Tab.Color = rgbFuchsia     'was ColorIndex=16751103/RGB(255,153,255)

It makes writing the code easier and representative colors replaced are sufficiently close as to be almost exact.

 

[Jamie Weeks]

-- removed inline image ---



That didn't work. I'm not sure what I did wrong. I pasted the code as you can see. I tried both suggestions (for the individual tab and then for the whole workbook) but alas, neither worked.

 

[Jamie Weeks]

wow, that was awful. sorry about that. I tried to paste in two screen snips to show what I did.

 

[Eric W]

Rhodie, excellent point. Using the color names makes the code much more readable. I think vbBlue is a better choice than rgbLimeGreen though.

Jamie, as you've seen, you can't post pictures here! It is possible to upload your picture to a file sharing service, then include the link here.

When you say it didn't work, do you mean it didn't do anything? Or did it do something, but something wrong? Do you have macros enabled for the workbook? If you go back to the VBA editor (Alt-F11), find the Immediate window (if it's not there, press Control-G) and type:

print application.EnableEvents

and see if it says True or False.

 

[Jamie Weeks]

I meant that it didn't work at all. However, once I re-opened the file, after saving and closing, it worked! ta-da! Thank you all so much for your help! I'm pretty sure my boss thinks I'm a wizard now

Kind Regards,

Jamie