Excel

[KC_Cheer_Coach]

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.

 

[KC_Cheer_Coach]

I am using Excel 2003.

 

[Per Jessen]

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

 

[KC_Cheer_Coach]

Thank you. This works if I put the cell address in, but the cell address
changes. Can the cell address already have a formula in it? If I make a cell
change to, lets say D for done when the time is entered for the trigger line
item, then can I say something like:

If ((Sh.Range(Cells(2, 8), Cells(30, 8)).Value = "D")

Or is that not allowed? Everywhere I have looked tells me it can't be done,
but I am thinking it has to be possible somehow without copying code into 48
sheets.

Thanks!

 

[Per Jessen]

Hi

Sure it's not desireable to paste the code into 48 sheets.

You can set up a loop to check if any cell in the range has an D in
it.

Dim bolTriggerComplete As Boolean
For Each cell In sh.Range(Cells(2, 8), Cells(30, 8))
If cell.Value = "D" Then
bolTriggerComplete = True
Exit Sub
End If
Next
If bolTriggerComplete Then
'"D" is found in range
'Do what needs to be done
End If

Hopes this helps

 

[KC_Cheer_Coach]

Thanks a lot! This worked great but I had some additional conditions so I was
able to take this and tweak it to what I needed. Thanks a million!