Hi

Names for ranges has to be unique, so therefore if you try to name a
range on sheet2 as a name used on sheet1, the first reference will be
deleted!

If you replace ...Range("TriggerComplete").... with a cell
reference ...Range("A1").... it will look at the value in A1 in the
active sheet.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim iRange As Range
If Not (Intersect(Target, Range(Cells(2, 8), Cells(30, 8))) Is
Nothing) Then
If ((Sh.Range("A1").Value 0) And (Sh.Range("A2").Value = 0))
Then
Sh.Tab.ColorIndex = 3
ElseIf ((Sh.Range("A1").Value 0) And (Sh.Range("A2").Value
0)) Then
Sh.Tab.ColorIndex = 5
Else
Sh.Tab.ColorIndex = xlColorIndexNone
End If
End If
End Sub

Hopes this helps

---
Per



On 28 Mar., 03:30, KC_Cheer_Coach
wrote:
I am using Excel 2003.



"KC_Cheer_Coach" wrote:
I have an Excel workbook with 49 tabs. Each tab starting with sheet 2 has a
list of tasks to be completed. Once each line item is signed and the
analyst's time is entered, the line item turns blue (meaning complete). The
job cannot be closed until all line items are blued out.

The next to the last line item that is completed, is a trigger for the
Processor to go in and close the job out and blue the last item.

I would like to write something so that when the trigger line item has time
entered, the tab turns red, then when the Processor closes the job, the sheet
tab turns blue.

**Note: The Trigger and Processor line items are not located in the same
place on every worksheet.

I defined the two time cells as TriggerComplete and ProcessComplete and have
placed this code under "This Workbook" :

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim iRange As Range

If Not (Intersect(Target, Range(Cells(2, 8), Cells(30, 8))) Is Nothing) Then

* * If ((Sh.Range("TriggerComplete").Value 0) And
(Sh.Range("ProcessComplete").Value = 0)) Then
* * * * Sh.Tab.ColorIndex = 3
* * ElseIf ((Sh.Range("TriggerComplete").Value 0) And
(Sh.Range("ProcessorComplete").Value 0)) Then
* * * * Sh.Tab.ColorIndex = 5
* * Else
* * * * Sh.Tab.ColorIndex = xlColorIndexNone
* * End If
End If
End Sub

This works great, but only if I am changing time on the second worksheet. I
tried to define the cell names on the other worksheets but it either doesn't
save or changes the reference to a different sheet, thus making the code only
work if I change time on that particular sheet.

Can anyone help? I would greatly appreciate it.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -