Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet tab color
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet tab color
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" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet tab color
Thanks for the help i just got it to work.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
color fill button is not adding color to my spread sheet | Excel Worksheet Functions | |||
How can I change sheet tab color based on cell value in sheet? | Excel Programming | |||
Sheet Background Color | Excel Discussion (Misc queries) | |||
sheet tab color in Excel X | Excel Discussion (Misc queries) | |||
sheet tab color | Excel Programming |