Conditional Formatting - Worksheet Tabs

**Nubbers** · 12-10-2007, 11:32 AM

Heya People's!

What I would like to do is change the colour of a worksheet tab. I only want the worksheet tab to change colours though if a condition in that one worksheet becomes true (goes into effect), if there is no condition, or if the condition returns back to being false, then I want the tab to remain in its default setting.

Is changing the tabs like this even possible? If it is possible, can anyone point me into the right direction of doing this?

Thanks in advance,
Nubbers

**Nubbers** · 12-12-2007, 02:31 PM

Bump! Completely re-edited the question to be more understandable! (is that even a word?!)

**VBA Noob** · 12-12-2007, 02:39 PM

See if this gets you started

Right click the sheet tab > select view code > paste in the below

If A1 is > then 10 the sheet tab will turn Red otherwise it will stay grey

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
    If Target.Value > 10 Then
        ActiveWorkbook.ActiveSheet.Tab.ColorIndex = 3
    Else
        ActiveWorkbook.ActiveSheet.Tab.ColorIndex = xlNone
End If
    End If
End Sub

VBA Noob

**Nubbers** · 12-12-2007, 04:46 PM

Wow thanks for the quick reply!

I was trying to make it easy and just find a way to use the code you gave in my spreadsheet, the code worked if I manually put in the number, but if I had a formula in the cell it wouldn't work...

I was wondering if it's possibly for the code to look at the whole worksheet, and if one or more of the cells has red font, to make the tab red, and if none of the cells has red font, to put it back to its default. I've been trying to figure out how to write this myself, but my vba skills are extremely limited.

Thanks again!!
Nubbers

**VBA Noob** · 12-12-2007, 04:58 PM

Not great with code myself but give this a try

Amended

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng As Range
Set Rng = Range("A1:A10")

  If CountByColor(Rng, 3, True) >= 1 And _
  WorksheetFunction.CountA(Rng) >= 1 Then
        ActiveWorkbook.ActiveSheet.Tab.ColorIndex = 3
    Else
        Rng.Font.ColorIndex = 0
        ActiveWorkbook.ActiveSheet.Tab.ColorIndex = xlNone
End If
  
End Sub

Sorry

Code in normal module to count font colour from Chip pearson site

Function CountByColor(InRange As Range, _
    WhatColorIndex As Integer, _
    Optional OfText As Boolean = False) As Long
'
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim Rng As Range
Application.Volatile True

For Each Rng In InRange.Cells
If OfText = True Then
    CountByColor = CountByColor - _
            (Rng.Font.ColorIndex = WhatColorIndex)
Else
    CountByColor = CountByColor - _
       (Rng.Interior.ColorIndex = WhatColorIndex)
End If
Next Rng

End Function

VBA Noob

**Nubbers** · 12-12-2007, 05:22 PM

Hey vba noob, thanks for all the help!!! you've helped me go so far!!!

The only problem is, the code works, only if I physically go in and change the font colour to red, the problem is I have it conditionally formatted to change red, for some reason, if the condition changes the number to red, the macro doesn't pick it up

I'm going to continue looking to see if I can find anything!

Thanks again,
Nubbers