VBA Code for changing colors to multiple excel tabs

[pjandshelly]

First off, I would like to start that I have very little VBA experience. I had an issue and looked this up online. I have a workbook that has a cover sheet to it. On the cover sheet, there are 30 reports that I must complete. On the cover sheet also, I have a value that I change for each worksheet that is a 1 or 0 for each report. Every week, I am responsible for updating the different tabs data report. Some of the tabs do not need to be updated weekly. I would like a VBA code to change the color of the tab based off the 1 or 0 value. I found this link online and tried to replicate the code, but it isn't working. https://www.extendoffice.com/documents/excel/4090-excel-color-tab-based-on-cell-value.html#a2

In the VBA screen, I am clicking on the Cover sheet in the Microsoft Excel Objects. Then pasting this code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Updateby Extendoffice 20160930
Select Case Sheets("Cover").Range("F6").Value
Case "1"
Sheets("NLT%Appts").Tab.Color = vbGreen
Case "0"
Sheets("NLT%Appts").Tab.Color = vbRed
Case "KTW"
End Select
Select Case Sheets("Cover").Range("G6").Value
Case "1"
Sheets("TIU%Unsigned").Tab.Color = vbGreen
Case "0"
Sheets("TIU%Unsigned").Tab.Color = vbRed
Case "KTW"
End Select
End Sub

I would continue this script until I get the 30 tabs completed. Then save the worksheet as XLSM.

Can anyone point me to my error?

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Clr
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("F6:AI6")) Is Nothing Then
      Clr = Choose(Target.Value + 1, vbRed, vbGreen)
      Select Case Target.Address(0, 0)
         Case "F6"
            Sheets("NLT%Appts").Tab.color = Clr
         Case "G6"
            Sheets("TIU%Unsigned").Tab.color = Clr
      End Select
   End If
End Sub

 

[pjandshelly]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Clr
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("F6:AI6")) Is Nothing Then
      Clr = Choose(Target.Value + 1, vbRed, vbGreen)
      Select Case Target.Address(0, 0)
         Case "F6"
            Sheets("NLT%Appts").Tab.color = Clr
         Case "G6"
            Sheets("TIU%Unsigned").Tab.color = Clr
      End Select
   End If
End Sub

So my values go down, so I changed mine to F6:F42, but this threw an error when it got to the first Case Statement. One other thing to note, I don't necessarily go in order from F6, F7. I may go F6, F20, F15. Would this be an issue with the color changing? Also, the tab is actually named NLT Appts, TIU Unsigned. I just know from previous dealings with code, Spaces are not allowed and usually replaced by a % sign. Could that be a part of the problem?

See duplicated code below:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Clr
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("F6:F42")) Is Nothing Then
Clr = Choose(Target.Value + 1, vbRed, vbGreen)
Select Case Target.Address(0, 0)
Case "F6"
Sheets("NLT%Appts").Tab.Color = Clr
Case "F7"
Sheets("TIU%Unsigned").Tab.Color = Clr
End Select
End If
End Sub

 

[Fluff]

Change the sheet names to exactly match your actual sheet names & it should work.
As long as the intersect range covers all the cells you are interested in, then simply refer to the individual cells in the Case Statement & all should be ok.

 

[pjandshelly]

Awesome, that works like a charm.

 

[Fluff]

You're welcome & thanks for the feedback