Change tab color based on cell value

Vantiro

New Member
Joined
Jul 25, 2014
Messages
15
Afternoon all,

I've read a bunch of different posts regarding VBA code to change the color of a tab based on a condition but haven't been able to get any of them to work.

I have about 100 different worksheets/tabs that are similiar, and I want to change each individual tab color based on the value of a cell D20 in each tab. If the value is 0 then I want no color the tab. If it is anything but 0 I want it to be green. Cell D20 is a formula that summs a bunch of cells within that tab.

I've tried variations of the code below but it does not seem to do anything. Also tried recalculating the workbook but no luck.

Private Sub Worksheet_Change(ByVal Target As Range)If Range("D20").Value <> 0 Then Me.Tab.ColorIndex = 10Else Me.Tab.ColorIndex = xlColorIndexNoneEnd IfEnd Sub</PRE>Thanks for any help!
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,669
This is code for a ThisWorkbook module.

To install the code:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and double-click the 'Thisworkbook' icon.
3. Copy the code below from your browser window and paste it into the white space in the VBE window.
4. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
5. Make sure you have enabled macros whenever you open the file or the code will not run.
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Range("D20") = 0 Then
    Sh.Tab.ColorIndex = xlColorIndexNone
Else
    Sh.Tab.Color = vbGreen
End If
End Sub
 
Last edited:

Vantiro

New Member
Joined
Jul 25, 2014
Messages
15
Thanks for the quick reply.

I followed those steps exactly but it is not giving any action. If I change cell D20 from a formula to a hard entered value, then it will change the tab to green. Otherwise, it leaves the tab 'no color' regardless of the value calculated in cell D20. Also, there are 5 tabs that I want to exclude this conditional formatting from. I am using excel 2010.
 
Last edited:

Vantiro

New Member
Joined
Jul 25, 2014
Messages
15
I ended up going with this below, seems to work! Thanks!

Code:
Private Sub Worksheet_Calculate()
    If Range("D20").Value = 0 Then
        Me.Tab.ColorIndex = -4142   ' No Color
    Else
        Me.Tab.ColorIndex = 10       ' Green
    End If
    
End Sub
 

Forum statistics

Threads
1,082,373
Messages
5,365,074
Members
400,822
Latest member
Aldebaran13

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top