#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default Excel

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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default Excel

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.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 703
Default Excel

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 -


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default Excel

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" wrote:

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 -



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 703
Default Excel

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

---
Per

On 28 Mar., 17:08, KC_Cheer_Coach
wrote:
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" wrote:
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 -- Skjul tekst i anførselstegn -


- Vis tekst i anførselstegn -




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default Excel

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!

"Per Jessen" wrote:

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

---
Per

On 28 Mar., 17:08, KC_Cheer_Coach
wrote:
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" wrote:
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 -- Skjul tekst i anførselstegn -


- Vis tekst i anførselstegn -



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 12:51 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"