How To Change Tab Color In Excel

Wouldn't information technology be great to alter the color of a worksheet tab based on a calculation?  If you're non convinced, hither are some possible scenarios:

• When a schedule reconciles or includes a specific jail cell value
• If there is an error in a range of cells
• To color code tabs to match a contents page

Yous were unsure at first, weren't y'all?  But now you call up it would exist a adept idea, don't you lot?  The skilful news is, it'due south pretty simple.

It just requires a pocket-size amount of lawmaking in a User Defined Part.

I didn't recall upward this idea past myself.  The idea came from David Hager'southward post, check out his post hither.

Creating the User Defined Function

Follow these instructions if the Programmer Tab is non visible.

Display the Developer ribbon

Correct-click on a bare part of the ribbon and select Customize the Ribbon… from the menu

From the Excel Options window click Customize Ribbon, enable the Developer option then click OK.

To create a User Defined Office click Developer -> Visual Basic (or shortcut ALT + F11).

The Visual Basic Editor window volition open, click Insert -> Module

Add the lawmaking as shown below.  The code for User Defined Functions must be within a standard module to work correctly.

VBA Lawmaking:

          Function          TabColor(CellColor          As Range,          Optional          SheetName          As String, _          Optional          WorkbookName          Every bit String)  Awarding.Volatile          If          SheetName = ""          So          SheetName = Application.Caller.Parent.Proper noun          Finish If          If          WorkbookName = ""            Then          WorkbookName = Application.Caller.Parent.Parent.Name          End If          If          CellColor.Interior.Color = xlNone          Then                    'If cell colour is no fill and so make tab colour automatic          Workbooks(WorkbookName).Sheets(SheetName).Tab.Color = xlAutomatic          Else                    'Else change to the cell color          Workbooks(WorkbookName).Sheets(SheetName).Tab.Colour = CellColor.Interior.Color          Cease If          TabColor = ""          End Function        

Depending on how you lot wish the User Defined Part to operate, there are a multifariousness of ways to use this office.  See the examples in the section below.

Using the Function

The Function we created above is now bachelor inside Excel for use, just similar any other part.  The syntax is equally follows:

=TabColor(CellColor,[SheetName],[WorkbookName])

• CellColor is a reference to a prison cell containing the colour to exist used
• SheetName is an optional argument to apply the office to some other sheet in the aforementioned workbook
• WorkbookName is an optional statement to apply the role to some other sheet in a dissimilar workbook

At present, let's wait at some examples.

Instance #1 – Bones usage

This examples shows the basic usage of the TabColor User Defined Function.

Case 1a

Cell A1 contains the following formula:

=TabColor(B1)

The tab color in Jail cell B1 is applied to the canvass within which the function is used.

Instance 1b

Cell A2 contains the following formula:

=TabColor(B2,"Sheet2")

The tab color in Cell B2 is applied to Sheet2 in the aforementioned workbook.

Example 1c

Cell A3 contains the following formula:

=TabColor(B3,"Sheet1","TestBook.xlsx")

The tab color in Jail cell B3 is applied to Sheet1 of the TestBook.xlsx workbook (which needs to be open up for the function to work).

Example #2: Where a prison cell includes a specific cell value

This example shows how to set the tab colour based on a prison cell value.

The formula in cell C4 is:

=IF(B4=100,tabcolor(C6),tabcolor(C7))

Where the Total equals 100 the tab color is green, otherwise it is reddish.

Example #three: Error in a range

The example shows the tab colour changing where an error exists within a range.

The formula in Cell D2 is:

=IF(SUMPRODUCT(--ISERROR(B2:B9))>=1,TabColor(D3),TabColor(D4))

The SUMPRODUCT element of the formula, counts the number of errors.  As there is 1 error in Cell B7 the IF function will show the TRUE consequence, which is causing the TabColor function to turn the tab colour carmine.

Instance #4: Color code tabs to a contents page

This example shows the Canvas Proper name being constructed from two cells.

The formula in Jail cell D4 is:

=TabColor(D4,$B4&" "&D$3)

NY in Cell B4 and Sales in Cell D3 are concatenated with the " & " symbol.  The TabColor role is causing the "NY Sales" tab to turn light green.

As the TabColor office is non using calculated values it is able to betoken to information technology's own cell without causing a circular reference.

---

---

Use RGB to employ the colour

In the UDF above, the colour is ready past linking to a cell, by changing the code information technology is possible to utilize the RGB values instead.

          Function          TabColorRGB(Red          Every bit Integer, Dark-green          Equally Integer, Blue          As Integer, _                      Optional          SheetName          As String,          Optional          WorkbookName          As Cord)  Awarding.Volatile          If          SheetName = ""          Then          SheetName = Awarding.Caller.Parent.Name          Stop If          If          WorkbookName = ""            And then          WorkbookName = Application.Caller.Parent.Parent.Name          Stop If          Workbooks(WorkbookName).Sheets(SheetName).Tab.Color = RGB(Red,Greenish,Bluish)  TabColor = ""          Stop Role        

This function can be used as follows:

=TabColorRGB(255,0,0,"Sheet1","TestBook.xlsx")

The outset 3 arguments are the Carmine, Green and Blue color codes.  The Sheet proper name and Workbook proper name are both optional.  The formula above will turn the tab for Sheet1 of TestBook.xlsx to a red (which is the color of Red = 255, Greenish = 0 and Blueish = 0)

Determination

Irresolute the tab color using a formula is quite easy and can provide a simple visual representation of the condition of a worksheet within the workbook.

---

Get our Gratuitous VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel and then that you lot can save time and stop doing the jobs a trained monkey could do.

Past entering your email address you agree to receive emails from Excel Off The Grid. We'll respect your privacy and yous can unsubscribe at any time.

---

Don't forget:

If you lot've plant this mail useful, or if yous accept a better approach, and then please leave a comment below.

Practice yous need help adapting this to your needs?

I'm guessing the examples in this postal service didn't exactly meet your situation.  Nosotros all use Excel differently, so information technology's impossible to write a post that volition meet everybody's needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) y'all should be able to accommodate it to your needs.

Just, if you're still struggling you lot should:

1. Read other blogs, or watch YouTube videos on the same topic.  Yous will benefit much more by discovering your ain solutions.
2. Ask the 'Excel Ninja' in your office.  It's astonishing what things other people know.
3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Think, the people on these forums are generally giving their fourth dimension for gratis.  So take care to arts and crafts your question, make sure information technology'due south clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What adjacent?
Don't go notwithstanding, there is enough more to larn on Excel Off The Grid.  Check out the latest posts:

Source: https://exceloffthegrid.com/change-tab-color-based-on-a-cell-value-in-excel/

Posted by: claytongeoppons.blogspot.com

Share this post