Change a tab color based on a cell value with a formula automatically

[crystaljulius]

Hi, i am an accountant and need the Tab color to change based on a cell value with a formula automatically. I currently have the below code. It works but only when I click on to the Tab. Can this be changed so that when I update my master sheet it automatically changes the tab color without clicking on the tab.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("F18").Value <> 0 Then
Me.Tab.ColorIndex = 4
Else
Me.Tab.ColorIndex = xlColorIndexNone
End If
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
You can use the calculate event instead, although this will run when any formula on the sheet recalculates, so it could slow your workbook down.

Private Sub Worksheet_Calculate()
If Range("F18").Value <> 0 Then
Me.Tab.ColorIndex = 4
Else
Me.Tab.ColorIndex = xlColorIndexNone
End If
End Sub

 

[Jimmypop]

Hi and Welcome...

My suggestion would maybe be the below...(Not sure how many sheets you need this to work on... This gets placed in the ThisWorkbook section and will update the mentioned sheets Tab color whenever a different sheet... maybe Master Sheets is updated I linked my test with a formula)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Worksheets("Sheet1").Range("F18").Value <> 0 Then
        Worksheets("Sheet1").Tab.ColorIndex = 4
    Else
        Worksheets("Sheet1").Tab.ColorIndex = xlColorIndexNone
    End If
End Sub

 

[Fluff]

That will only work if a cell is changed manually, not via a formula.

 

[Peter_SSs]

What is the formula in F18?
What tab is that formula on?

 

[Jimmypop]

Hi Fluff... Apologies...maybe I should have stated that what I did with my test is I put on Sheet1 in F18 the following:

=Sheet2!A1

If on Sheet 2 I change the number then the tab color does change...

 

[Georgiboy]

I think to avoid calculations etc... I would create a lookup table containing each sheet name and next to it a link to the cell (i.e. F18), you could even have the colour you want the tab to go next to that.

Then i would create a macro that would fire whenever you leave the 'Master' sheet that would then colour the tabs based on the lookup table that was created.

The benefit of doing it this way will mean that the tabs colours would only be changed once when you leave the Master tab, the drawbacks are having to keep the lookup table updated every time you add a new sheet.

The only event that will ever be fired with this method is the one when leaving the Master sheet. (Worksheet_Deactivate)

Lookup table would look something like the below:

Book1
	A	B	C
1	Sheet Name	Link to cell	Colour
2	Sheet2	0	vbGreen
3	Sheet3	7	vbRed
4	Sheet4	0	vbBlue
Sheet1

Cell Formulas
Range	Formula
B2	B2	=Sheet2!F18
B3	B3	=Sheet3!F18
B4	B4	=Sheet4!F18

 

[Georgiboy]

Changed it slightly, so I decided to keep to ColorIndex as that was what was being used.

Lookup tab named: Lookup
Table on the Lookup tab:

Book1
	A	B	C
1	Sheet Name	Link to cell	Colour Index
2	Sheet2	0	4
3	Sheet3	7	4
4	Sheet4	0	4
Lookup

Cell Formulas
Range	Formula
B2	B2	=Sheet2!F18
B3	B3	=Sheet3!F18
B4	B4	=Sheet4!F18

Code that I put into the Master tab:

Private Sub Worksheet_Deactivate()
    Dim wsLookup As Worksheet
    Dim tLookup As Range
    Dim rCell As Range
    
    Set wsLookup = Sheets("Lookup")
    Set tLookup = wsLookup.ListObjects("Table1").ListColumns(1).DataBodyRange
    
    For Each rCell In tLookup.Cells
        If rCell.Offset(, 1) <> 0 Then
            Sheets(rCell.Value).Tab.ColorIndex = rCell.Offset(, 2).Value
        End If
    Next rCell
End Sub

 

[crystaljulius]

Hi & welcome to MrExcel.
You can use the calculate event instead, although this will run when any formula on the sheet recalculates, so it could slow your workbook down.

Private Sub Worksheet_Calculate()
If Range("F18").Value <> 0 Then
Me.Tab.ColorIndex = 4
Else
Me.Tab.ColorIndex = xlColorIndexNone
End If
End Sub

Thank you. This works. If I have any problems will try the other solutions.

 

[Fluff]

Glad we could help & thanks for the feedback.