Skip i'r prif gynnwys

Sut i newid lliw tab dalen yn seiliedig ar werth celloedd?

Fel rheol, gallwch chi newid lliw y tab yn hawdd ac yn gyflym yn Excel, ond, a ydych chi erioed wedi ceisio newid lliw'r tab yn seiliedig ar werth celloedd mewn taflen waith? Os oes gennych ddiddordeb yn y dasg hon, byddaf yn siarad am rai codau i chi liwio'r tab dalen yn seiliedig ar werth celloedd penodol yn Excel.

Newid lliw tab un ddalen yn seiliedig ar werth celloedd gyda chod VBA

Newid tabiau dalennau lluosog yn seiliedig ar werth celloedd gyda chod VBA

swigen dde glas saeth Newid lliw tab un ddalen yn seiliedig ar werth celloedd gyda chod VBA

Er enghraifft, rwyf am y bydd lliw tab y ddalen gyfredol yn wyrdd os mai gwerth y gell yn A1 yw'r testun “GWIR”, bydd lliw'r tab yn goch os yw'r testun yn A1 yn “GAU”, a bydd lliw y tab yn las os y gwerth yng nghell A1 yw unrhyw destun arall fel y dangosir y llun a ganlyn:

taflen liw doc yn ôl gwerth 1

1. Cliciwch ar y dde ar y tab dalen rydych chi am newid ei liw yn seiliedig ar y data yng nghell A1, ac yna dewiswch Gweld y Cod o'r ddewislen cyd-destun.

2. Yn y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr, copïo a gludo'r cod VBA isod i'r Modiwlau ffenestr.

Cod VBA: Newid lliw tab un ddalen yn seiliedig ar werth celloedd:

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

taflen liw doc yn ôl gwerth 2

Nodyn: Yn y cod uchod, A1 yw'r cyfeirnod cell rydych chi am liwio'r tab yn seiliedig arno, “Cywir","Anghywir”Yw'r testun sydd ei angen arnoch chi, gallwch eu newid i'ch angen, a gallwch chi newid y lliw yn y cod yn ôl yr angen.

3. Yna arbedwch a chau y ffenestr god hon, nawr, pan fyddwch chi'n nodi'r testun “Gwir” yng nghell A1, bydd lliw cyfredol y tab yn dod yn wyrdd, a phan fyddwch chi'n nodi'r testun “Anghywir” yng nghell A1, bydd lliw'r tab yn dod yn goch, ac os yw testun arall yn cael ei roi yn y gell A1, bydd lliw y tab yn dod yn las yn awtomatig.

swigen dde glas saeth Newid tabiau dalennau lluosog yn seiliedig ar werth celloedd gyda chod VBA

Os oes angen i chi newid lliw tabiau dalennau lluosog yn seiliedig ar werth cell, dyma hefyd god a allai eich helpu chi, gwnewch fel a ganlyn:

1. Daliwch i lawr y ALT + F11 allweddi i agor y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr, yn y ffenestr a agorwyd, cliciwch ddwywaith Llyfr Gwaith hwn O dan y Prosiect VBA adran, yna copïwch a gludwch y cod isod i'r gwag Modiwlau:

Cod VBA: Newid tabiau dalennau lluosog yn seiliedig ar werth celloedd:

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

taflen liw doc yn ôl gwerth 3

Nodyn: Yn y cod uchod, A1 a Master yw'r gell a'r daflen waith rydych chi am liwio'r tab yn seiliedig arni, Sheet1, Sheet2, Sheet3 yw'r taflenni gwaith rydych chi am liwio'r tabiau. KTE, KTW, KTO yw'r gwerthoedd celloedd yn A1 rydych chi am liwio'r tabiau yn seiliedig arnyn nhw, gallwch chi newid y cyfeiriadau a'r lliwiau yn y cod yn ôl yr angen.

2. Yna arbedwch a chau y ffenestr god hon, nawr, pan fyddwch chi'n nodi'r testun KTE yng nghell A1 y ddalen Feistr, bydd tab Sheet1 wedi'i liwio mewn coch, pan fyddwch chi'n mynd i mewn i KTO yng nghell A1, bydd y Daflen 2 wedi'i lliwio'n wyrdd, a phryd rydych chi'n mynd i mewn i KTW yng nghell A1, bydd y Daflen3 wedi'i lliwio mewn glas, gweler y screenshot:

taflen liw doc yn ôl gwerth 4

Offer Cynhyrchiant Swyddfa Gorau

🤖 Kutools AI Aide: Chwyldro dadansoddi data yn seiliedig ar: Cyflawniad Deallus   |  Cynhyrchu Cod  |  Creu Fformiwlâu Personol  |  Dadansoddi Data a Chynhyrchu Siartiau  |  Invoke Swyddogaethau Kutools...
Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog  |   VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ....
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ....
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr   |  Rheolwr Llyfr Gwaith a Thaflen   |  Llyfrgell Adnoddau (Testun Auto)   |  Dewiswr Dyddiad   |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd   |  Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau:  12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   ... a mwy

Supercharge Eich Sgiliau Excel gyda Kutools ar gyfer Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed. Kutools ar gyfer Excel Yn Cynnig Dros 300 o Nodweddion Uwch i Hybu Cynhyrchiant ac Arbed Amser.  Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...

Disgrifiad

Mae Office Tab yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
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