View Full Version : Solved: Coloring sheet tab conditionnally
mehdoush
05-22-2009, 02:00 AM
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
05-22-2009, 02:34 AM
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
05-22-2009, 04:59 AM
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
05-22-2009, 05:40 AM
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
05-22-2009, 06:54 AM
i agree you're right
regards
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.