Conditional Formatting an Excel Sheet Tab Based on Value in a Cell From a Formula

Question

I am not a VBA code writer by any stretch of the imagination, but I was trying to dabble in the following:

The goal: Make a sheet tab color turn green when cell B3 = Complete or it turns yellow if cell B3 = Open.

The "problem": Cell B3 has a formula:=IF(SUM(B6:B8)=0,"Complete","Open").

I tried using the code below (found it from a search) and modified it a little to what I needed. Left the "Case Else" section in it just not to mess with the code too much and figured only the first 2 conditions were ever going to be met anyway.

It works, but only when you manually type "Complete" or "Open" in cell B3 as the value. I want to keep the formula, but the code sees the formula and not the value of the formula. After much reading and trying to understand VBA, maybe I need Worksheet_Calculate() somewhere?

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    If Target.Address = "$B$3" Then
        Select Case Target.Value`your text`
        Case "Open"
            Me.Tab.Color = vbYellow
        Case "Complete"
            Me.Tab.Color = vbGreen
        Case Else
            Me.Tab.Color = vbBlue
        End Select
    End If
End Sub
        

Answer 1

You could do this:

Private Sub Worksheet_Calculate()
    With Me.Range("B3").DisplayFormat
        If .Interior.ColorIndex = xlNone Then
            Me.Tab.ColorIndex = xlColorIndexNone
        Else
            Me.Tab.Color = .Interior.Color
        End If
    End With
End Sub

The tab color will follow the cell's fill color (or clear if there is no CF-applied fill)