Change tab color based on cell value

[Vantiro]

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]

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.

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

 

[Vantiro]

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.

 

[Vantiro]

I ended up going with this below, seems to work! Thanks!

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