Tab Name & Color Change Based on Value

[r0bism123]

Morning gang!

I finally got the code below to work with a color change, but I am struggling to get it to change the corresponding tab name. My goal with the code is to have cells A16 to A83 on my rent schedule tab, change the corresponding tab name and color with the content therein.

For example, if I enter the name "Tenant 1" I want the tab name to change to "Tenant 1" and turn yellow. However, if I enter the word "Vacant", I want the tab to change to "Vacant" and turn red only for Vacant suites.

Any help getting this to work for me would be huge! Thank you in advance for your time!

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
On Error GoTo M
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Row > 15 Then
If Target.Value = "Vacant" Or Target.Value = "vacant" Then
Sheets(Target.Row + 1).Tab.Color = RGB(255, 76, 76)
Else
Sheets(Target.Row + 1).Tab.Color = RGB(255, 248, 66)
End If
End If
End If
Exit Sub
M:
MsgBox "That sheet number  " & (Target.Row - 0) & "  does not exist"
End Sub

 

[My Aswer Is This]

Thanks for helping out here mse.

To protect & unprotect the workbook & sheet you can use the below

' To unprotect before your code
ActiveWorkbook.Unprotect Password:="xyz"
Sheets(1).Unprotect Password:="xyz"
​
'Protect back again
ActiveWorkbook.Protect Password:="xyz"
Sheets(1).Protect Password:="xyz"

 

[r0bism123]

To protect & unprotect the workbook & sheet you can use the below

' To unprotect before your code
ActiveWorkbook.Unprotect Password:="xyz"
Sheets(1).Unprotect Password:="xyz"
​
'Protect back again
ActiveWorkbook.Protect Password:="xyz"
Sheets(1).Protect Password:="xyz"

Yes, I have been successful in protecting the worksheet, however, when I protect the workbook structure, my code stops working. Thoughts?

 

[mse330]

Thanks for helping out here mse.

You are welcome

 

[mse330]

Yes, I have been successful in protecting the worksheet, however, when I protect the workbook structure, my code stops working. Thoughts?

Try to add the below line … What happens ?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Dim ans As Long
On Error GoTo M
ActiveWorkbook.Unprotect Password:="Your Password here"
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Target.Row > 15 Then
            ans = Target.Row
            If Target.Value = "Vacant" Or Target.Value = "vacant" Then
                Sheets(ans + 1).Tab.Color = RGB(255, 76, 76)
                Sheets(ans + 1).Name = Sheets(ans + 1).Cells(5, 3).Value
            Else
                Sheets(ans + 1).Tab.Color = RGB(255, 248, 66)
                Sheets(ans + 1).Name = Target.Value
             End If
        End If
    End If
   
ActiveWorkbook.Protect Password:="Your Password here"
   
Exit Sub
M:
MsgBox "Error: Duplicate Tenant Name"
ActiveWorkbook.Protect Password:="Your Password here"
End Sub