1

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
4
  • I think you need to use .Value instead of .Text.
    – Kyle
    Jan 25, 2016 at 16:26
  • Is it just one particular sheet you want the colour to change for?
    – Jonno
    Jan 25, 2016 at 16:30
  • Kyle: I changed it to Value and it still works manually, but not when the formula changes the cell. Jonno: Yes - just one based upon the formula 1-9 in the cell in sheet 'MEDENT Proposal - Creator'!B15 I think that it must have something to do with the fact that it is a function in Cell A that pulling the value from sheet 'MEDENT Proposal - Creator'!B15 I would be happy if the vba was pulling directly from sheet 'MEDENT Proposal - Creator'!B15 instead of "A1"
    – Ed Regis
    Jan 25, 2016 at 16:36
  • @EdRegis use @ to reply to people, that way they will get a notification.
    – Kyle
    Jan 25, 2016 at 17:07

1 Answer 1

1
  1. You can make it work even when it's a formula referencing another worksheet by using the Worksheet_Calculate event instead.
  2. The Worksheet_Calculate won't fire when you manually change the cell so you'll need both if you want both scenarios to work.
  3. You'll need to drop the code into each worksheet module you want to be affected.
  4. You should use Me.Tab instead of ActiveSheet.Tab unless you want it to color whatever tab you happen to be on which you probably don't.
  5. You can really shorten your Select Case statement if you convert the value to an actual integer.

Private Sub Worksheet_Calculate()
    ColorMyTab
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    ColorMyTab
End Sub

Private Function ColorMyTab()
    Dim myVal As Integer
    myVal = 99
    On Error Resume Next
        myVal = CInt(Range("A1").Value)
    On Error GoTo 0

    With Me.Tab
        Select Case myVal
            Case 0
                .Color = vbRed
            Case 1 To 9
                .Color = vbGreen
            Case Else
                .ColorIndex = xlColorIndexNone
        End Select
    End With
End Function

If you want the code to simply watch the cell value on the MEDENT sheet, you can write a very similar script in the code for that worksheet module. Since it's watching just that sheet, though, you'd have to hard-code which tabs you want it to color. Does the MEDENT sheet control the color for several tabs? Are those tab names stored in column A:A the values you're changing are in column B:B? If you give us more information about what your ultimate goal is, we can recommend better solutions.

1
  • No problem, @EdRegis. Welcome to SuperUser. Jan 25, 2016 at 17:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .