Solved Macro change tab color based on font color

Microsoft Office excel 2007 home & stude...
December 20, 2011 at 17:26:56
Specs: Windows XP
I was hoping someone can help me with a Macro.

I have an Excel Spreadsheet that has numerous tabs. Throughout the workbook there are cells that have the font color of orange. I have been going to each tab and checking to see if there are any cells that have orange font and changing the tab color to orange. Was wondering if there is a Macro that can loop through all the sheets and determine and check for any orange font in the all the worksheets and if there is change the tab color to match accordingly.

For Example:
Loop through the sheets to determine how many sheets...
Check Sheet 1 for any orange font in any cells in sheet1. If there is, change tab color to orange. Check Sheet 2 for any orange font, if any, then change the tab color to orange...etc...

I am using Microsoft Excel 2007.

Hope this makes sense. Thanks in advance for any help!


See More: Macro change tab color based on font color

Report •


#1
December 21, 2011 at 16:40:09
First you post a question asking how to change all Tabs that are colored Orange to be No Color, then you post a question asking how to turn Tabs Orange.

Make up your mind! ;-)

Anyway, once again we have the issue of what color Orange do you have. Excel 2003 was much more limited in choices for colors, so this type of thing was much easier to do than it is in 2007 or higher.

The first thing you need to do is determine what the ColorIndex number for your Orange font is.

You can do that with these steps:

1 - Paste this code into the VBA editor:

Sub WhatOrange()
 MsgBox Selection.Font.Colorindex
End Sub

2 -Find a cell with an Orange font color and select it.
3 - Run the code
4 - Jot down the number shown in the Message Box
5 - Click OK

Once you have that number, paste this code into the VBA editor:

Sub ColorMyTab()
 For shtNum = 1 To Sheets.Count
  For Each cell In Sheets(shtNum).UsedRange
   If cell.Font.ColorIndex = 44 Then
     Sheets(shtNum).Tab.ColorIndex = 44
     Exit For
   End If
  Next
 Next
End Sub

Now replace the 44 (twice) with whatever ColorIndex number you jotted down from the previous macro.

Run the 2nd piece of code.

One very important note:

The only way that I know to find a specific Font.ColorIndex in a spreadsheet is to check each cell in the UsedRange one by one.

If you have huge spreadsheets, with thousands of cell in the UsedRange, the code could take a long time to run. The code will stop checking each shet as soon as it finds a cell with a Font.ColorIndex that matches what's in the code, so if it's A1, things will go quickly. However, if it's something like RDC120376, it's going to take a while to get there.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 21, 2011 at 18:03:08
Yes...I am sorry DerbyDad03...every week a spreadsheet comes to me updated through multiple tabs. I go through each tab in the workbook and change the tab color orange (for those that have orange font in the cells) so I can go back and update. After all the updates are complete...I change the tab colors back to normal, so they can be updated again....it's a vicious cycle...so I thought if there was a Marco that can help me update the tab colors automatically so I don't have to go one by one...and then afterwards...go back and change them back to normal one by one....

The color Index that returned is 46. When I try to run the second piece of code (changing 44 to 46), when it ran...it actually changed all the tab colors orange...it changed the tabs orange that didn't have orange font within that sheet.

Thanks for all you help...It is much appreciated!


Report •

#3
December 21, 2011 at 20:28:04
✔ Best Answer
I pasted the ColoyMyTab code into a brand new workbook, changed both 44's to 46 and ran it.

As expected, no tabs were changed to orange.

I then selected 1 cell in Sheet1 and 1 cell in Sheet3 and ran the following code to set both cell's Font.IndexColor to 46.

Sub SetOrange()
 Selection.Font.ColorIndex = 46
End Sub

I did nothing to Sheet2.

When I ran ColorMyTab again, only the Tabs for Sheets 1 and 3 were changed to Orange, as expected.

That tells me that there is indeed at least one cell in all of your sheets that has a Font.IndexColor of 46. It's what the code checks for and it will only change the tab color if it finds it.

Try this...

1 - Pick a sheet where you think there are no cells with a Font.IndexColor of 46. (a sheet where you think the tab color should not have been changed)
2 - Determine the number of that sheet, counting the tabs from left to right.
3 - Replace the number 1 in the code below with the number of the sheet you picked in Step 1. (The code below will check the left most sheet (1), regardless of what the tab name is. You'll want to use the sheet number of a sheet where you think the tab should not have been changed to Orange.)
4 - Run the code.

I'm guessing that it is going to pop up a message box with an address in it.

Sub CheckFor46()
 shtNum = 1
  For Each cell In Sheets(shtNum).UsedRange
   If cell.Font.ColorIndex = 46 Then
     MsgBox Sheets(shtNum).Name & "!" & cell.Address
     Exit For
   End If
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
December 22, 2011 at 10:32:38
This worked...found some hidden cells in the spreadsheet that were orange font!

Once again thanks for all you help! It's really appreciated! Makes my job soo much easier.


Report •

#5
December 22, 2011 at 10:53:34
I'm glad you found the hidden cells and I'm glad to have been of assistance.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
January 5, 2012 at 15:36:41
Hi DerbyDad03...I have a question for you...

Is there a way were the macro can be modified or added to to inlcude the following...

Currently the Macro runs and looks for any cells with orange font. However, within a given cell there may be orange font that is not being picked up by the Macro. It will have regular text (black/automatic color font) and then have orange as well. The Macro currently looks for the font color of the text in the cell not just part of the text.

For example...
Micorsoft Excel will be in a given cell. However Microsoft is balck color font while Excel is in orange font. Is there a way to adjust/modify/add to the Macro to pick up these type of example?

Thanks in advance


Report •

#7
January 5, 2012 at 19:44:13
In order to find cells that only have one or more characters that are Orange, the code would need to check each character in each cell one by one until it found a character that was Orange.

Depending on how much data is in your workbook this could take a pretty long time. On slow systems I've seen those types of macros slow the machine to a crawl and sometimes even cause it become un-responsive.

The code is not that difficult to write, but it could really be a long slow process.

Are you sure that you want to do that?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
January 6, 2012 at 09:23:16
Yes please. If you can write the code for me, and I can try it out...if it causes my system to be slow or unresponsive, then I can choose not to have run the code or not if that is the case. It will be good to have just in case....

Thanks in advance!


Report •

#9
January 6, 2012 at 10:21:13
Sub ColorMyTab()
 For shtNum = 1 To Sheets.Count
  For Each cell In Sheets(shtNum).UsedRange
   For charNum = 1 To cell.Characters.Count
    If cell.Characters(charNum, 1).Font.ColorIndex = 46 Then
     Sheets(shtNum).Tab.ColorIndex = 46
     Exit For
    End If
   Next
  Next
 Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
January 6, 2012 at 15:53:35
Awesome!

DerbyDad03 thank you so much! It's greatly appreciated!


Report •

Ask Question