Sheet tab color

[Leon]

I have 1 sheet per tenant with their information and X amount of
tenants. The 4 cells that the macro looks at in each sheet are
different dates. For example Lease End Date is one of them. So 1st part
of the If is if the date of the lease end is today or past today make
the sheet tab Red. If its today +30 make it yellow so i know the lease
is ending. If its neither it makes it white. If one cell is true the
loop stops and jumps to the next sheet

Code:
Sub test()

Dim ws As Worksheet, rng
rng = Array(13, 16, 22, 27)
For Each ws In Worksheets
x = Application.Match(ws.Name, Array("AT&T Lease", "as"), 0)
If Not IsError(x) Then
With ws
For i = 0 To UBound(rng)
flag = False
Select Case .Range("b" & rng(i)).Value
Case Is <= Date
''''''.Tab.ColorIndex = 3
Application.Run ("TabRed"): flag = True
Case Is < Date + 30
''''''.Tab.ColorIndex = 6
Application.Run ("TabYellow"): flag = True
Case Else
''''' .Tab.ColorIndex = -4142
Application.Run ("TabWhite"): flag = False
End Select
If flag Then Exit For
Next
End With
End If
Next
End Sub
[/Code]

The application "TabRed" is: ActiveWorkbook.Sheets("AT&T
Lease").Tab.ColorIndex = 3

I think "TabRed" is code i need to change to make this all work
correctly. But i tried to insert: ".Tab.ColorIndex = 3" instead and it
error's on me.

Is there any way to say change the sheet tab color that the loop is on
and not specify a sheet?

Thanks everyone in advance.

 

[Patrick Molloy]

Sub SetTabColors()
Dim ws As Worksheet
Dim clr As Long
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Range("A1").Value
Case 1: clr = 3 'red
Case 2: clr = 6 'yellow
Case Else: clr = 2 'none
End Select
ws.Tab.ColorIndex = clr
Next
End Sub

 

[Leon]

Thanks for the help i just got it to work.