Colored Tabs in Excel for Words, Not Numbers

Hi,

Could you send a dummy example with before and after results

Regards

Hi Camille.

You could just put something like the code below into the "ThisWorkbook" object in VBA. It looks for a non-numeric value in cell G2 for each sheet, then sets the tab yellow for those that have such a value. On the Developer tab, under the Macros button, you can just select the UpdateTab_Color function and Run it to test.

Does this help?

Sub UpdateTab_Color()
Dim Worksh As Worksheet
Dim MyStatus As Boolean

For Each Worksh In ThisWorkbook.Worksheets

Worksh.Activate
        If Range("G2").Value = "AA" Then
             With Worksh.Tab
                .Color = 65535
            End With
        End If
Next Worksh
End Sub

Select allOpen in new window

bcnagel, why put that in the Workbook Module? It's also unnecessary to select sheets for something like this. Also the With Statement isn't really any advantage.

I would use the Workbook_SheetDeactivate event. You would really need to have a cell to check, in my example A1

In the VB EDitor in the left hand drop down choose WorkBook, in the right hand drop select Workbook_SheetDeactivate. Paste this code:

Option Explicit

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Not IsNumeric(Sh.Range("A1").Value) Then Sh.Tab.Color = vbRed
End Sub

Select allOpen in new window

EE-Sheet-Tab-Colour.xlsm

Thanks Roy. I only had a few minutes to grab some code I've used before and rework it--that's why the sheet select & with statements. Using SheetDeactivate is great. I just wanted to give Camille a way to test the code--I wasn't sure when or how she wants to run the code.

I'd love to hear back from Camille if we understood her question well enough.

BCNagel and Roy,

I so appreciate your help! However, I am not tech savvy enough to figure out how to use the coding. Which section on the Excel sheet should I input the code? I am not sure what VBA means...

Rgonzo1971,

I know under the conditional formatting tab I am able to edit my settings on my document filled with numerical values to be colored depending on the values within each cell. I just can't seem how to figure out that same methodology if my cells contained words rather than numbers. Maybe it isn't a setting, and I would have to do it manually. Just thought I would ask the pros to gain advice.

Thank you, all!

As far as I'm aware, we can't change tab colors with conditional formatting, Camille. Is that what you're trying to do? So, you want to color the names of the various sheets as below?

BCNagel,

I am looking to create the color scale on cells that contain words rather than numbers. In the image below, I have used the conditional formatting scale to denote the value of numbers. I want to apply a similar color scheme to my words on the left side.

I hope that helps clarify my question. Thank you for being so helpful!
EE-Q-1.PNG

Are you working with Tabs or cells?

This is it!! Thank you so much for your help!!