Sheet color based on cells value

[draqdragos]

Hi!

So, i have this huge worksheet of 101 sheets. First "DATA" is jus that, a data sheet from which all the other 100 sheets are taking up data with lookup function.
I need the 100 sheets to change colour based on cell value from each sheet. Cell is question is "F14" on each sheet, or column on data sheet.
All I need is for the sheets where value in said cell is less then 50 to turn red.

Appreciate any help you can give be.

Thank you, have a Merry Christmas and Happy Holidays!

 

[smozgur]

Welcome to the MrExcel Message Board!

If you mean changing the sheet tab color, then it can be done for all worksheets, except DATA, by using the following VBA code. To use the code, goto VBE, insert a new standard Module, copy and paste the code into this new module, and finally Run the code.
Note: The code will also change the hidden worksheets' tab color if there is any meeting the criteria. If this is not desired, then an additional condition can be added to the first If...End If block.

Sub changeSheetTabColors()
Dim sht As Worksheet
    ' Loop through all worksheets in this workbook
    For Each sht In ThisWorkbook.Worksheets
        With sht
            ' Do not change the DATA worksheet tab color
            If sht.Name <> "DATA" Then
                If IsNumeric(.Range("F14")) And .Range("F14") < 50 Then
                    ' If F14 is a numeric value and less than 50, then set the color as red
                    .Tab.Color = vbRed ' Or for various colors: RGB(255, 0, 0)
                Else
                    ' Otherwise set the color as default
                    .Tab.ColorIndex = xlColorIndexNone
                End If
            End If
        End With
    Next sht
End Sub

 

[draqdragos]

Works like a charm!

Cheers! ?

 

[smozgur]

Works like a charm!

Cheers! ?

Glad to hear it helps! Thanks for the feedback.