CODEWITHSUNDEEP
excelvba
Petkau
Petkau

Reputation: 11

Change tab colour based on cell value, runtime error

On each tab of my workbook, cell I1 calculates Print if the tab should be printed (meets criteria)

I am trying to code each tab to look at I1 and change tab colour if I1 = "Print". All of the codes that I am trying end up resulting in a Runtime error 1004.

Can anyone see what is wrong with my code?

EDIT 1: The error is thrown up at

.Color = RGB(255, 255, 102)

EDIT 2: I have switched to Calculate from Change. Thanks for that.

EDIT 3: I have two module codes in the same book which password protect/unprotect all tabs. Could this be interfering?

Sub protect_all_sheets()  
top:  
pass = InputBox("password?")  
repass = InputBox("Verify Password")  
If Not (pass = repass) Then  
MsgBox "you made a boo boo"  
GoTo top  
End If  
 For i = 1 To Worksheets.Count  
 If Worksheets(i).ProtectContents = True Then GoTo oops  
 Next  
 For Each s In ActiveWorkbook.Worksheets  
 s.Protect Password:=pass  
 Next  
 Exit Sub  
 oops: MsgBox "I think you have some sheets that are already protected. Please unprotect all sheets then running this Macro."  
End Sub  


Private Sub Worksheet_Change(ByVal Target As Range)
    MyVal = Range("I1").Text

    With ActiveSheet.Tab
        Select Case MyVal
            Case "Print"
                .Color = RGB(255, 255, 102)
            Case Else
                .ColorIndex = xlColorIndexNone
        End Select
    End With
End Sub

Upvotes: 1

Views: 128

Answers (1)

Tim Williams
Tim Williams

Reputation: 166860

Worksheet_Change isn't triggered by calculations: you need Worksheet_Calculate

Private Sub Worksheet_Calculate()
    Me.tab.color = IIf(Me.Range("I1").value="Print",vbRed,vbWhite)
End Sub

Upvotes: 1

Related Questions