2014-06-03, 08:46 #1
- Join Date
- Jun 2014
- Thanked 0 Times in 0 Posts
Change sheet tab color in Excel 2010 based on conditionally formatted cells
I am new to WSLounge and fairly new to some of the more advanced functions of Excel 2010 but I'm working on a project and trying to learn my way through changing the worksheet tab color of my spreadsheet based on several individual cells that are conditionally formatted on only that specific worksheet.
For example, TODAY() is in cell D16. I have a conditionally formatted rule that if that date is less than or equal to 3 days from EOMONTH and the dropdown menu I've created in D17 has "INCOMPLETE" selected then D16 will turn Red. I'm looking for a way to make the worksheet tab turn red if D16 is red based on those conditions. D16 is also formatted to turn yellow for a range of 4-10 days from EOMONTH and purple if 11-15 days from EOMONTH. I have several cells on several sheets within my workbook formatted this way and want to color the corresponding sheet tabs to alert the user if there are red cells within that sheet.
Is this possible? I'm having a hard time finding information on this specific task and am a complete novice when it comes to writing code....
Thank you SO much in advance if you can help me figure this out.
Subscribe to our Windows Secrets Newsletter - It's Free!
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!
2014-06-03, 09:14 #2
- Join Date
- Jul 2002
- Pittsburgh, Pennsylvania, USA
- Thanked 338 Times in 331 Posts
This will trigger based on changing D17. If you need to change under other circumstances you will have to let us know. Be aware this type of macro will disable UNDO in the entire spreadsheet.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("D17")) Is Nothing Then If Range("D17") = "INCOMPLETE" And _ DateSerial(Year(Date), Month(Date) + 1, 0) - Range("D16") <= 3 Then Me.Tab.Color = 255 Else Me.Tab.ColorIndex = xlNone End If End If End Sub
PS, if you have lots of cells that you want to test, you may want to use a formula to look for any "conditions me" and then use a calculate macro to loop through the sheets and change the colors of those meeting the conditions to red and those that do not to non-colored instead of creating code to check for each and every occurence...
Last edited by sdckapr; 2014-06-03 at 09:18.
2014-06-04, 07:34 #3
- Join Date
- Dec 2000
- Burwash, East Sussex, United Kingdom
- Thanked 80 Times in 76 Posts
You could use something like this:
Private Sub Worksheet_Calculate() Me.Tab.ColorIndex = Range("D16").DisplayFormat.Interior.ColorIndex End SubRegards,
Microsoft MVP - Excel.
The Following User Says Thank You to rory For This Useful Post: