Macro For Tab Colors if Value is Greater Than or Less Than

[davcurnutt]

I have a large spreadsheet and I want to turn tabs green if a value in a specified column exceeds 0.01 and yellow if a value is less than -0.05, otherwise no color. This is my current macro for green and white. How can I add the yellow?

Sub Tabcolor()
'
' Tabcolor Macro
'
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
If Application.WorksheetFunction.CountIf(ws.Range("O1:O900"), ">0.01") > 0 Then
ws.Tab.Color = vbGreen
Else
ws.Tab.Color = vbWhite
End If
Next ws


'
End Sub

 

[kevin9999]

Try the following on a copy of your workbook.

Sub Tabcolor()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If Application.WorksheetFunction.CountIf(ws.Range("O1:O900"), ">0.01") > 0 Then
            ws.Tab.Color = vbGreen
        ElseIf Application.WorksheetFunction.CountIf(ws.Range("O1:O900"), ">-0.05") > 0 Then
            ws.Tab.Color = vbYellow
        Else
            ws.Tab.Color = vbWhite
        End If
    Next ws
End Sub

 

[davcurnutt]

Try the following on a copy of your workbook.

Sub Tabcolor()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If Application.WorksheetFunction.CountIf(ws.Range("O1:O900"), ">0.01") > 0 Then
            ws.Tab.Color = vbGreen
        ElseIf Application.WorksheetFunction.CountIf(ws.Range("O1:O900"), ">-0.05") > 0 Then
            ws.Tab.Color = vbYellow
        Else
            ws.Tab.Color = vbWhite
        End If
    Next ws
End Sub

Awesome. Thank you. The only thing I changed was

ElseIf Application.WorksheetFunction.CountIf(ws.Range("O1:O900"), ">-0.05") > 0 Then to

ElseIf Application.WorksheetFunction.CountIf(ws.Range("O1:O900"), "<-0.05") > 0 Then

Thank you

 

[kevin9999]

Happy to help, and thanks for the feedback