Auto tab colour

[Semper Paratus]

Hi all,

Is it possible to create a macro to automatically change the colour of a tab depending on what is in a particular cell?

Either:
Cell A1 is blank so tab is red or Cell A1 contains a name (eg. John Smith or David Jones) so tab is green
OR
Cell A1 is blank and tab is neutral, or cell A1 is 'No' so tab is red, or cell A1 'Yes' so tab is green.

 

[mole999]

Dynamically Changing Worksheet Tab Color (Microsoft Excel)

 

[Semper Paratus]

Hi and thanks for the assistance, however I have a small issue as I already have a macro running and now with the second one it will not work, so my next query is how do I have two macros running on the same page?

I currently have in ThisWorkBook
Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
If Target.Address <> "$B$7" Then Exit Sub
ActiveSheet.Name = Target.Value
End Sub

And this in Sheet 1
Private Sub Worksheet_Change(ByVal Target As Range)
MyVal = Range("A1").Text

With ActiveSheet.Tab
Select Case MyVal
Case "0"
.Color = vbBlack
Case "1"
.Color = vbRed
Case "2"
.Color = vbGreen
Case "3"
.Color = vbYellow
Case "4"
.Color = vbBlue
Case "5"
.Color = vbMagenta
Case "6"
.Color = vbCyan
Case "7"
.Color = vbWhite
Case Else
.ColorIndex = xlColorIndexNone
End Select
End With
End Sub


So how do I get them working side by side?

 

[mole999]

Maybe Flip It

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
MyVal = Range("A1").Text

 With ActiveSheet.Tab
 Select Case MyVal
 Case "0"
 .Color = vbBlack
 Case "1"
 .Color = vbRed
 Case "2"
 .Color = vbGreen
 Case "3"
 .Color = vbYellow
 Case "4"
 .Color = vbBlue
 Case "5"
 .Color = vbMagenta
 Case "6"
 .Color = vbCyan
 Case "7"
 .Color = vbWhite
 Case Else
 .ColorIndex = xlColorIndexNone
 End Select
 End With
 
 If Target.Address <> "$B$7" Then Exit Sub
 ActiveSheet.Name = Target.Value
 End Sub

 

[Semper Paratus]

Thaat did not work unfortunately, I am not sure why, but depending on which macro I put in first, the first one will work and the second won't... trying to put them both on one sheet seems to mean they both don't work, like they cancel each other out.

 

[mole999]

Thaat did not work unfortunately, I am not sure why, but depending on which macro I put in first, the first one will work and the second won't... trying to put them both on one sheet seems to mean they both don't work, like they cancel each other out.

I combined the macros into one, did you copy and paste what I showed into the sheet, make sure you don't have two Workbook_SheetChange which should through an error when you compile