Sheet tab color

[Leon]

Hello everyone this is my situation/problem:

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.

 

[Jay Freedman]

Hi Leon,

You're in the wrong newsgroup -- this one is for Word, not Excel. Post
your question in microsoft.public.excel.programming.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Leon]

Oh sorry about that. Thanks for the heads up.