Excel VBA:自动调整标签颜色 [英] Excel VBA: automatically adjust tab colour

问题描述

我有一个单元格("L2")，该单元格由if函数控制，可以根据其他字段中的用户输入来确定通过"，错误"或检查框".

I have a cell ("L2") that is controlled with an if function to determine either "PASS", "ERROR" or "CHECK BOX" based on user input in other fields.

我希望每当单元格L2的值更改时，相应选项卡的颜色就会根据单元格内容自动更改.

I want the colour of that respective tab to change automatically based on cell content whenever the value of cell L2 changes.

我的代码当前看起来像这样，但是不幸的是，它不起作用.可以请您帮我看看吗?非常感谢您的帮助！

My code currently looks like this, but unfortunately, it doesn't work. Can you please have a look and help me out? Many thanks for your help!

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(False, False) = "L2" Then

    Select Case Target.Value
        Case "ERROR"
            Me.Tab.ColorIndex = 3
        Case "PASS"
            Me.Tab.ColorIndex = 4
        Case Else
            Me.Tab.ColorIndex = xlColorIndexNone
    End Select

End If
End Sub

我注意到了一些有趣的事情:由于单元格"L2"的显示值是通过其中包含的if函数来控制的，因此vba似乎并不读取显示的值，而是从单元格中读取if语句.我该如何解决?

I noticed something interesting: As the displayed value of cell "L2" is controlled via an if function that is contained within it, the vba does not appear to read the displayed value, but rather the if-statement from the cell. How can I get around this?

感谢您的帮助.

推荐答案

如果您希望根据L2中的值更改选项卡颜色，请使用以下代码:

In case you want the tab color to change depending on the value in the L2 then use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Range("$L$2").Value
        Case "ERROR"
            Me.Tab.ColorIndex = 3
        Case "PASS"
            Me.Tab.ColorIndex = 4
        Case Else
            Me.Tab.ColorIndex = xlColorIndexNone
    End Select
End Sub

这篇关于Excel VBA:自动调整标签颜色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！