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
CodePudding user response:
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 not CF-applied fill)
CodePudding user response:
As far as I know, you can't do this.
The Worksheet_Calculate event IS fired when the cell with the formula updates but there is no way to check if the event was triggered by the cell you are interested in or something else entirely.
What you might be able to do instead, is look into what caused the cell to update in the first place and check for that.
What @TimWilliams might be hinting at with his comment is that you could do something like this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim checkRange As Range
Set checkRange = Range("B6:B8")
If Not Application.Intersect(checkRange, Target) Is Nothing Then
If range("B3").Value = "Complete" Then
Me.Tab.Color = vbGreen
Else
Me.Tab.Color = vbYellow
End If
End If
End Sub
But this will only work when the cells in B6-B8 range are changed manually.
You could also just do:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B3").Value = "Complete" Then
Me.Tab.Color = vbGreen
Else
Me.Tab.Color = vbYellow
End If
End Sub
This will update the tab color every time anything changes on the sheet, not just B3 or B6:B8.