I have a formula for Automatic Tab color change that works when I manually change the cell, but it does not work when the cell is changed by a function updating it from another sheet: =SUM('MEDENT Proposal - Creator'!B15) Question 1. Can I just change my VBA to look directly at the cell in the other sheet 'MEDENT Proposal - Creator'!B15 ? Question 2. Is there another way to write this VBA so that it will automatically update the Tab Colors - is the following written incorrectly ? Again, it works if I manually change Cell "A1" in that sheet...
Private Sub Worksheet_Change(ByVal Target As Range)
MyVal = Range("A1").Text
With ActiveSheet.Tab
Select Case MyVal
Case "0"
.Color = vbRed
Case "1"
.Color = vbGreen
Case "2"
.Color = vbGreen
Case "3"
.Color = vbGreen
Case "4"
.Color = vbGreen
Case "5"
.Color = vbGreen
Case "6"
.Color = vbGreen
Case "7"
.Color = vbGreen
Case "8"
.Color = vbGreen
Case "9"
.Color = vbGreen
Case Else
.ColorIndex = xlColorIndexNone
End Select
End With
End Sub
.Value
instead of.Text
.