跳到主要內容

如何根據單元格值更改工作表標籤的顏色？

通常，您可以在Excel中輕鬆快速地更改選項卡顏色，但是，您是否曾經嘗試過根據工作表中的單元格值更改選項卡顏色？ 如果您對此任務感興趣，我將為您介紹一些代碼，這些代碼可讓您根據Excel中的特定單元格值為工作表標籤著色。

使用VBA代碼根據單元格值更改一張標籤的顏色

使用VBA代碼根據單元格值更改多個工作表標籤

箭頭藍色右氣泡 使用VBA代碼根據單元格值更改一張標籤的顏色

例如，我希望如果A1中的單元格值為文本“ TRUE”，則當前工作表標籤顏色將為綠色；如果A1中的文本為“ FALSE”，則標籤顏色將為紅色；如果A1中的文本為“ FALSE”，則標籤顏色將為藍色。單元格AXNUMX中的值是任何其他文本，如以下屏幕截圖所示：

doc色表（按值1）

1。 右鍵單擊要基於單元格A1中的數據更改其顏色的工作表選項卡，然後選擇 查看代碼 從上下文菜單。

2。 在 Microsoft Visual Basic for Applications 窗口，將下面的VBA代碼複製並粘貼到 模塊 窗口。

VBA代碼：根據單元格值更改一種工作表標籤的顏色：

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    If Target.Address = "$A$1" Then
        Select Case Target.Value
        Case "False"
            Me.Tab.Color = vbRed
        Case "True"
            Me.Tab.Color = vbGreen
        Case Else
            Me.Tab.Color = vbBlue
        End Select
    End If
End Sub

doc色表（按值2）

注意： 在上面的代碼中， A1 是您要根據以下選項為標籤上色的單元格引用：真“”假”是您需要的文本，您可以根據需要對其進行更改，還可以根據需要在代碼中更改顏色。

3。 然後保存並關閉此代碼窗口，現在，當您在單元格A1中輸入文本“ True”時，當前標籤頁顏色將變為綠色，而當您在單元格A1中輸入文本“ False”時，標籤頁顏色將變為紅色，如果在單元格A1中輸入了其他文本，則標籤顏色將自動變為藍色。

箭頭藍色右氣泡 使用VBA代碼根據單元格值更改多個工作表標籤

如果您需要根據單元格值更改多個工作表標籤的顏色，以下代碼也可以為您提供幫助，請執行以下操作：

1。 按住 ALT + F11 鍵打開 Microsoft Visual Basic for Applications 窗口，在打開的窗口中，雙擊 的ThisWorkbook 在 VBA項目 部分，然後將以下代碼複製並粘貼到空白處 模塊:

VBA代碼：根據單元格值更改多個工作表標籤：

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  'Updateby Extendoffice 20160930
  Select Case Sheets("Master").Range("A1").Value
         Case "KTE"
             Sheets("Sheet1").Tab.Color = vbRed
         Case "KTO"
             Sheets("Sheet2").Tab.Color = vbGreen
         Case "KTW"
             Sheets("Sheet3").Tab.Color = vbBlue
         End Select
End Sub

doc色表（按值3）

注意： 在上面的代碼中， A1 和Master是您要根據其為標籤著色的單元格和工作表， Sheet1, Sheet2, Sheet3 是要為選項卡著色的工作表。 KTE, 韓國電信, 韓國旅遊發展局 是您要為標籤上色的A1中的單元格值，您可以根據需要更改代碼中的引用和顏色。

2。 然後保存並關閉此代碼窗口，現在，當您在主表的單元格A1中輸入文本KTE時，Sheet1選項卡將變為紅色，當您在單元格A1中輸入KTO時，Sheet2將變為綠色，並且當在單元格A1中輸入KTW時，Sheet3將變為藍色，請參見屏幕截圖：

doc色表（按值4）

最佳辦公生產力工具

🤖 Kutools 人工智慧助手：基於以下內容徹底改變數據分析： 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup  |   多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧   |  工作簿和工作表管理器   |  資源庫 （自動文字）   |  日期選擇器   |  合併工作表   |  加密/解密單元格   |  按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 （過濾粗體/斜體/刪除線...）...
前 15 個工具集:  12 文本 工具 (添加文本, 刪除字符，...）   |   50+ 圖表 類型 (甘特圖，...）   |   40+ 實用 公式 (根據生日計算年齡，...）   |   19 插入 工具 (插入二維碼, 從路徑插入圖片，...）   |   12 轉化 工具 (數字到單詞, 貨幣兌換，...）   |   7 合併與拆分 工具 (高級合併行, 分裂細胞，...）   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能，體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

產品描述

Office選項卡為Office帶來了選項卡式界面，使您的工作更加輕鬆

  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
Comments (28)
No ratings yet. Be the first to rate!
Lesa
Lesa
This comment was minimized by the moderator on the site
I want every tab to turn Red if G18 is greater than 100. Could someone help me write this code?
Reply
0
0
skyyang
skyyang     Lesa
This comment was minimized by the moderator on the site
Hello, Lesa,

To modify the code so that when the value in G18 exceeds 100, the color of all worksheet tabs is set to red, you need to implement this functionality in the ThisWorkbook event, not in the Worksheet_Change event of a single worksheet.
1. Open the VBA editor (Alt + F11).
2. In the Project Explorer, find the ThisWorkbook object and double-click to open it.
3. Copy and paste the following code into the code window of ThisWorkbook:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Intersect(Target, Sh.Range("G18")) Is Nothing Then
        Dim ws As Worksheet
        If Sh.Range("G18").Value > 100 Then
            For Each ws In ThisWorkbook.Worksheets
                ws.Tab.Color = RGB(255, 0, 0)
            Next ws
        Else
            For Each ws In ThisWorkbook.Worksheets
                ws.Tab.ColorIndex = xlColorIndexNone
            Next ws
        End If
    End If
End Sub


Result:
If G18 > 100 in any sheet, it iterates through all the worksheets in the workbook, setting their tab color to red.
If the condition is not met, it resets the tab color of all worksheets to the default color.

Please have a try!
Reply
Report
0
0
Fabian
Fabian
This comment was minimized by the moderator on the site
Hi,


I used this code:

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Updateby Extendoffice 20160930
Select Case Sheets("Master").Range("A1").Value
Case "KTE"
Sheets("Sheet1").Tab.Color = vbRed
Case "KTO"
Sheets("Sheet2").Tab.Color = vbGreen
Case "KTW"
Sheets("Sheet3").Tab.Color = vbBlue
End Select
End Sub

The True, False or Else function works for me when I type it manually. However, I have a formula that displays True, False or Else as the result. This formula suddenly causes no response when changing the tab color. The formula is based on another sheet, is this a problem?
Reply
0
0
Clément LEGOUEST
Clément LEGOUEST     Fabian
This comment was minimized by the moderator on the site
Hello Fabian,

Same problem here, have you found any solution to solve this problem ?

Thanks.
Reply
0
0
Lauren
Lauren
This comment was minimized by the moderator on the site
I have a workbook that has 350+ tabs in it. I would like to change the color of each tab based on information in cell G3 on that individual tab. For example, if cell G3 on sheet 1 = 'Water Production' change the tab to green. Is there a way to do this for every tab without having to each tab individually?
Reply
0
0
Phil wood
Phil wood
This comment was minimized by the moderator on the site
I want to set the sheet tab color based on the color of cell j4 in each sheet. There are 18+ Tabs and want to update the tab colors when the workbook is opened. If I can’t update when opened I can run the macro after the teams update the sheets.
Reply
0
0
skyyang
skyyang     Phil wood
This comment was minimized by the moderator on the site
Hello, wood
To solve your problem, please apply the below code: (Note: Copy and paste the below code into the ThisWorkbook code mode)
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Call SetSheetColor
End Sub

Private Sub Workbook_Open()
Call SetSheetColor
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Call SetSheetColor
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Call SetSheetColor
End Sub

Sub SetSheetColor()
Dim xWShs As Sheets
Dim xRg As Range
Dim xFNum As Integer
Dim xSh As Worksheet
On Error Resume Next
Set xWShs = Application.ActiveWorkbook.Sheets
For xFNum = 1 To xWShs.Count
    Set xSh = xWShs.Item(xFNum)
    Set xRg = xSh.Range("J4")
    xSh.Tab.Color = xRg.Interior.Color
Next
End Sub


https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-color-sheet.png

Please have a try, hope it can help you!
Reply
Report
0
0
Susanne Dixson
Susanne Dixson
This comment was minimized by the moderator on the site
I would like my tab to change color if any cell in column O or column P has a value in it. Is this possible?

Thank you!
Reply
0
0
Maitri Radhesh
Maitri Radhesh
This comment was minimized by the moderator on the site
Hello ,
I require some help
I need to change the sheet tab color only if in a certain range of cells, has today's date
Say for example,
Column L has some dates in the format (13-May-22)
One of the cell value is today's date, then the tab sheet color should change to red
Please help
Thank you in advance
Reply
0
0
Justin
Justin
This comment was minimized by the moderator on the site
Im looking to change to color of the tab based off one of 2 outcomes of a formula I have a formula of=IF((AND(AA1="Occupied",AA2="Occupied",AA3="Occupied",AA4="Occupied")),"Occupied","Vacant")
I need the tab to be red if "Occupied" and green if "Vacant". However the code above in the main post doesn't recognize the output of the If Than
Reply
0
0
Brad
Brad
This comment was minimized by the moderator on the site
Hello, Trying to have tab colour change based on value on a seperate sheet called Tracking here is what I tried but it does not seem to be working. Thanks




Private Sub Worksheet_Change(ByVal Target As Range)

'Updateby Extendoffice 20160930

If Target.Address = "Tracking!$C$2" Then

Select Case Target.Value

Case "ip"

Me.Tab.Color = vbRed

Case "w"

Me.Tab.Color = vbYellow

Case "c"

Me.Tab.Color = vbGreen

Case Else

Me.Tab.Color = vbBlue

End Select

End If

End Sub
Reply
0
0
skyyang
skyyang     Brad
This comment was minimized by the moderator on the site
Hello, Brad,
To make the code run correctly, you should not insert the sheet name into the code, please apple the following code:(click the sheet name you want to run this code, and then right clik the sheet name, and choose View Code, then paste the code into the module)

Private Sub Worksheet_Change(ByVal Target As Range)

'Updateby Extendoffice 20160930

If Target.Address = "$C$2" Then

Select Case Target.Value

Case "ip"

Me.Tab.Color = vbRed

Case "w"

Me.Tab.Color = vbYellow

Case "c"

Me.Tab.Color = vbGreen

Case Else

Me.Tab.Color = vbBlue

End Select

End If

End Sub

Please try, hope it can help you!
Reply
Report
0
0
Brage
Brage
This comment was minimized by the moderator on the site
Hello,

Never done code in excel before. I need a code that do what this code do, but I need another parameter.

So what I need is:

If cell value is not 0, and/or if some other cell has numbers in it, using count, then change color to red

If cell value is 0 and those other cells are empty, using count, then change color to green
Reply
0
0
Rothera Rob
Rothera Rob
This comment was minimized by the moderator on the site
How would you link the tab to a value in a different tab. In my example I have one tab with all the information that filters out to various tabs automatically. However each tab will read green or red, depending on whether there is a bAlance outstanding on the master tab. Can this be done using this code and if so, where do I write the link to the master sheet within this code?
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations