Automatically Update Based on Change to Cell Value via Formula

[jl815]

Hi Guys-

I'm new to VBA and in need of your help updating this code. This code works:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then
Select Case Target.Value
Case "In Progress"
Me.Tab.Color = vbRed
Case "Closed"
Me.Tab.Color = vbGreen
Case "Open"
Me.Tab.Color = vbYellow
Case Else
Me.Tab.Color = vbBlue
End Select
End If
End Sub

However when I combine it with this one in order to have it automatically run whenever cell B3 is updated (the value of B3 comes from a vlookup formula), I get an error message "Compile Error: Syntax Error"
---------

Private Sub Worksheet_Calculate()
Static oldval
If Range("$B$3").Value <> oldval Then
oldval = Range("$B$3").Value
'
Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$3" Then
Select Case Target.Value
Case "In Progress"
Me.Tab.Color = vbRed
Case "Closed"
Me.Tab.Color = vbGreen
Case "Open"
Me.Tab.Color = vbYellow
Case Else
Me.Tab.Color = vbBlue
End Select
End If
'
End If
End Sub



Thanks in advance!

 

[Yongle]

Welcome to MrExcel

Here is a simpler method which I think will work for you
Delete both subs and replace with one macro below

Private Sub Worksheet_Calculate()
    Select Case Range("B3").Value
        Case "In Progress"
            Me.Tab.Color = vbRed
        Case "Closed"
            Me.Tab.Color = vbGreen
        Case "Open"
            Me.Tab.Color = vbYellow
        Case Else
            Me.Tab.Color = vbBlue
    End Select
End Sub

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

For future posts
please click on icon <vba/> and paste code inside code tags (does not allow any formatting however) - looks like above


To highlight something in your code with colours or bold or italics, then click on<rich/> and paste code inside code tags - looks like below

Private Sub Worksheet_Calculate()
    Select Case Range("B3").Value
        Case "In Progress"
            Me.Tab.Color = vbRed
        Case "Closed"
            Me.Tab.Color = vbGreen
        Case "Open"
            Me.Tab.Color = vbYellow
        Case Else
            Me.Tab.Color = vbBlue
    End Select

 

[jl815]

Thank you so much! This works perfectly! I'll take your advice for future posts.