VBA code for changing tab color based on cell values

[youngguy]

I need to change all tab colors to correspond with the max value in a range of cells (I10:I30) of that tab. If the max value is less than 5 the tab needs to be green. Between 6-12, yellow. Greater than 12, red. Any help would be greatly appreciated

 

[VoG]

Try this

Sub TabColor()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    With ws
        Select Case WorksheetFunction.Max(.Range("I10:I30"))
            Case Is < 5: .Tab.ColorIndex = 4
            Case 6 To 12: .Tab.ColorIndex = 6
            Case Else: .Tab.ColorIndex = 3
        End Select
    End With
Next ws
End Sub

 

[youngguy]

Worked well...had to make the first case a <= and worked perfect.

 

[youngguy]

New question-In my excel workbook I have an intro sheet with hyperlinks to each following sheet. Is there a way I can make each cell with the sheets hyperlink the same color as that sheets tab? Example: Intro sheet cell A10 has the hyperlink to sheet 3. Sheet 3's tab color is green. How can I make A10's color green as well? Is there a way to have one code for a range of cells or do I need a code for each cell and corresponding sheet?

 

[VoG]

Please start a new thread.