Solved: Coloring sheet tab conditionnally

[mehdoush]

hey everyone,

how can i write a macro that colors sheet tab into "red" if:

1) sheet contains "zero" string
2) sheet contains "nine" string
3) sheet contains "nine" AND "zero" strings
4) sheet contains "nine" OR "zero" strings

otherwise sheet tab color turns into "Green"

thanks in advance for your help
regards

[Bob Phillips]

Public Sub Test()

With ActiveSheet

If Application.CountIf(.UsedRange, "*zero*") Or _
Application.CountIf(.UsedRange, "*nine*") Then

.Tab.ColorIndex = 3
Else

.Tab.ColorIndex = 10
End If
End With
End Sub

[mehdoush]

thank you this is very inspriring ;)

that makes ->




Set wb = ThisWorkbook
For Each ws In wb.Worksheets

Columns("a:a").AutoFit

If Application.CountIf(ws.UsedRange, "*99999*") Or _
Application.CountIf(ws.UsedRange, "*length=0.000000*") Then

ws.Tab.ColorIndex = 3
ElseIf Application.CountIf(ws.UsedRange, "*99999*") And _
Application.CountIf(ws.UsedRange, "*length=0.000000*") Then

ws.Tab.ColorIndex = 3

Else
ws.Tab.ColorIndex = 4
End If


Next ws

[mdmackillop]

ElseIf Application.CountIf(ws.UsedRange, "*99999*") And _
Application.CountIf(ws.UsedRange, "*length=0.000000*") Then
This is not needed. It cannot be True if the previous test is False.

[mehdoush]

i agree you're right

regards