Change Tab color based on tab name

[carura]

Hi All,

Im working on a worksheet for multiple evaluation forms.
I have 3 types of forms:
OTO
TRNG
RTR

I need a code or something to make the tab/sheet colors change once the type of form is declared on the tab name.

Example:

If I put "OTO Nov 17" as the tab name, it will change the tab color to blue
or if I put "RTR Nov 5" as the name it will change to green.

I usually put the evaluation type as the first word then the date the evaluation was done.

Any help would be appreciated.

Thanks in advance!

 

[tlowry]

Here’s a take on this.

I could not find an event that is raised on renaming a sheet, so I put it in SheetActivate. This means that when the sheet is activated, the color change wil occur. I don't think the difference is noticeable.

Put this in the “ThisWorkbook” in the VBA Project window
· open the application (use a test application at first)
· press F11
· Double Click ThisWorkBook on the left hand side
· Copy the code and paste it into the Right Hand Side
· Save

Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Application.ScreenUpdating = False
Select Case Left(ActiveSheet.Name, 3)

Case "OTO"

Application.ActiveSheet.Cells.Interior.ColorIndex = 37
makegrid
​

Case "RTR"

Application.ActiveSheet.Cells.Interior.ColorIndex = 35
makegrid
​

Case "TRN"
End Select
​

Application.ScreenUpdating = True
​

End Sub

Sub makegrid()

With Application.ActiveSheet.Cells.Borders(xlInsideVertical)

.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
​

End With
With Application.ActiveSheet.Cells.Borders(xlInsideHorizontal)

.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
​

End With
​

End Sub

 

[John Davis]

Hi All,

Im working on a worksheet for multiple evaluation forms.
I have 3 types of forms:
OTO
TRNG
RTR

I need a code or something to make the tab/sheet colors change once the type of form is declared on the tab name.

Example:

If I put "OTO Nov 17" as the tab name, it will change the tab color to blue
or if I put "RTR Nov 5" as the name it will change to green.

I usually put the evaluation type as the first word then the date the evaluation was done.

Any help would be appreciated.

Thanks in advance!

Maybe:

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

With ActiveSheet

    If Left(ActiveSheet.Name, 3) = "OTO" Then
    
        .Tab.ColorIndex = 4
        
    ElseIf Left(ActiveSheet.Name, 3) = "RTR" Then
    
        .Tab.ColorIndex = 33
        
    Else
    
        .Tab.ColorIndex = xlNone
        
    End If
    
End With
End Sub

Change the colors too your preferred shading.

 

[carura]

Hi John Davis!

You reply was what I was looking for.
It changes the color of the tab but it only worked for the condition
it doesnt recognize the second condition.

I mean, all forms with OTO changed in color, so it worked.
But the forms with RTR didnt change.

@tlowry
Thanks for trying, but your code changes the color of the whole sheet.
I only need the tab color to change.

 

[John Davis]

Hi John Davis!

You reply was what I was looking for.
It changes the color of the tab but it only worked for the condition
it doesnt recognize the second condition.

I mean, all forms with OTO changed in color, so it worked.
But the forms with RTR didnt change.

@tlowry
Thanks for trying, but your code changes the color of the whole sheet.
I only need the tab color to change.

It worked when I tested it. Did you place the code in the specified Workbook change event?

 

[carura]

Hey John,

I tried it again. It works!
I guess I had to click on cells on the sheet to make it work.
I thought it would automatically change every tab.

Thanks a lot!!

 

[John Davis]

You're welcome. I'm glad it worked for you, and thanks for the feedback.