Hi BeSmart,

You can only have one Worksheet_Change procedure in the worksheet module.

You need to combine the two into a single procedu:

Private Sub Worksheet_Change(ByVal Target As Range)

Set LastEnteredCell = Target

For Each c In Target
If Not Intersect(c, [M14:GR605]) Is Nothing Then
Target.Cells.Interior.ColorIndex = xlNone
If IsNumeric(Target.Value) Then
If Target.Value 0 Then
Select Case LCase(Cells(Target.Row, 5).Text)
Case Is = "Retail"
Target.Cells.Interior.ColorIndex = 38
Case Is = "Quick"
Target.Cells.Interior.ColorIndex = 37
Case Is = "Jewel"
Target.Cells.Interior.ColorIndex = 34
Case Is = "Brand"
Target.Cells.Interior.ColorIndex = 36
Case Is = "Other"
Target.Cells.Interior.ColorIndex = 2
Case Is = "Generic"
Target.Cells.Interior.ColorIndex = 2

End Select
End If
End If
End If
Next c
End Sub

I have not looked at the code itself.

---
Regards,
Norman



"BeSmart" wrote in message
...
Hi all
I have existing code on an Excel 2000 spreadsheet that looks like this:

================================================== ==
Option Explicit

Public LastEnteredCell As Range
---------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Set LastEnteredCell = Target
End Sub
-----------------------------------------------------------------------
Sub goBack()
LastEnteredCell.Select
End Sub
-------------------------------------------------------------------------
......(There are then ClickButton codes after this)
================================================== ===
The current code works perfectly.

I want to copy and paste some new Worksheet_change code (see it below)
that
was written in a different workbook and has to apply to this worksheet.
It applies conditional formatting based on 5 conditions when users enter a
value into a range.
When I paste this code into the top of the existing codes, the End Sub is
appears but the dividing line don't (merging my code with "Option
Explicit"
etc) and when I test the code I start getting "Compile errors", "Ambiguous
name detected: Worksheet_Change"

What am I doing wrong? How do I get this new code into my worksheet and
retain the prior functions? I tried pasting it to the end of the code -
but
this made no difference.

New code:
=================================================
Private Sub Worksheet_Change(ByVal Target As Range)

For Each c In Target
If Not Intersect(c, [M14:GR605]) Is Nothing Then
Target.Cells.Interior.ColorIndex = xlNone
If IsNumeric(Target.Value) Then
If Target.Value 0 Then
Select Case LCase(Cells(Target.Row, 5).Text)
Case Is = "Retail"
Target.Cells.Interior.ColorIndex = 38
Case Is = "Quick"
Target.Cells.Interior.ColorIndex = 37
Case Is = "Jewel"
Target.Cells.Interior.ColorIndex = 34
Case Is = "Brand"
Target.Cells.Interior.ColorIndex = 36
Case Is = "Other"
Target.Cells.Interior.ColorIndex = 2
Case Is = "Generic"
Target.Cells.Interior.ColorIndex = 2

End Select
End If
End If
End If
Next c
End Sub
==================================================
Appreciate any help
--
Thank for your help
BeSmart

Thanks Norman - that explains the errors, however putting the "Set
LastEnteredCell = Target" into the top section of the Workbook_Change isn't
helping, I'm still getting errors....

The tasks refer to completely separate functions within the workbook. So
perhaps you can help with the second one...

Task 1. Conditional formatting to apply to any cells that has had a value
entered by the user. The follow code already acheives this and works well.
===============================================
Private Sub Worksheet_Change(ByVal Target As Range)

For Each c In Target
If Not Intersect(c, [M14:GR605]) Is Nothing Then
Target.Cells.Interior.ColorIndex = xlNone
If IsNumeric(Target.Value) Then
If Target.Value 0 Then
Select Case LCase(Cells(Target.Row, 5).Text)
Case Is = "Retail"
Target.Cells.Interior.ColorIndex = 38
Case Is = "Quick"
Target.Cells.Interior.ColorIndex = 37
Case Is = "Jewel"
Target.Cells.Interior.ColorIndex = 34
Case Is = "Brand"
Target.Cells.Interior.ColorIndex = 36
Case Is = "Other"
Target.Cells.Interior.ColorIndex = 2
Case Is = "Generic"
Target.Cells.Interior.ColorIndex = 2

End Select
End If
End If
End If
Next c
End Sub
=======================================

Task 2. A user needs to select Cntrl+Shift+R to return to the last cell
they entered or changed on the worksheet, regardless of where they now are.

The current code is no longer doing this correctly, so can you suggest how I
would write these actions into codes.

Thanks for your help
BeSmart

Hi BeSmart,

Task 2. A user needs to select Cntrl+Shift+R to return to the last cell
they entered or changed on the worksheet, regardless of where they now
are.

The current code is no longer doing this correctly, so can you suggest how
I
would write these actions into codes.

In testing it worked for me.

When I suggested amalgamating your two Worksheet_Change procedures, I
assume that you did NOT delete your goBack sub?

---
Regards,
Norman



"BeSmart" wrote in message
...
Thanks Norman - that explains the errors, however putting the "Set
LastEnteredCell = Target" into the top section of the Workbook_Change
isn't
helping, I'm still getting errors....

The tasks refer to completely separate functions within the workbook. So
perhaps you can help with the second one...

Task 1. Conditional formatting to apply to any cells that has had a value
entered by the user. The follow code already acheives this and works
well.
===============================================
Private Sub Worksheet_Change(ByVal Target As Range)

For Each c In Target
If Not Intersect(c, [M14:GR605]) Is Nothing Then
Target.Cells.Interior.ColorIndex = xlNone
If IsNumeric(Target.Value) Then
If Target.Value 0 Then
Select Case LCase(Cells(Target.Row, 5).Text)
Case Is = "Retail"
Target.Cells.Interior.ColorIndex = 38
Case Is = "Quick"
Target.Cells.Interior.ColorIndex = 37
Case Is = "Jewel"
Target.Cells.Interior.ColorIndex = 34
Case Is = "Brand"
Target.Cells.Interior.ColorIndex = 36
Case Is = "Other"
Target.Cells.Interior.ColorIndex = 2
Case Is = "Generic"
Target.Cells.Interior.ColorIndex = 2

End Select
End If
End If
End If
Next c
End Sub
=======================================

Task 2. A user needs to select Cntrl+Shift+R to return to the last cell
they entered or changed on the worksheet, regardless of where they now
are.

The current code is no longer doing this correctly, so can you suggest how
I
would write these actions into codes.

Thanks for your help
BeSmart

Hi Norman
I haven't deleted the goBack sub I'm getting errors because of Option
Elicit...

First the code came up with an error of:
"Compile Error" Only Comments may appear after Sub End..." because after the
Worksheet_Change code End Sub there was:

Option Elicit
Public LastEnteredCell As Range
--------------------------------------------------------------------------
Sub goBack()
LastEnteredCell.Select
End Sub

I understand this error because Option Elicit ... is outside a Sub / End Sub.
So I got rid of Option Elicit (because it not really necessary????) and I
moved "Public LastEnteredCell As Range" below "Sub goBack()" but then I got
this error and keep getting it no matter what I try:
"Object variable or With block variable not set"
================================================== ===
Sub goBack()
Public LastEnteredCell As Range
LastEnteredCell.Select
End Sub
================================================== ===
I know I need to add "With" or "For Each" something to the Sub goBack() for
it to work, but I don't know where/what to put? (Sorry I'm a novice and it
gets confusing)
Thanks for your help

Hi BeSmart,

Firstly,

Option Elicit should be Option Explicit

Next,

So I got rid of Option Elicit (because it not really necessary????)

It is not *necessary* but it is highly desirable. For future reference, see
Chip Pearson's notes at:

http://www.cpearson.com/excel/variables.htm

I have made a couple of changes to your code:

(1) I have explicitly declared your c variable as a range object. The Option
Explicit statement at the head of your module requires all variables to be
declared.

(2) I have changed

[M14:GR605] to Range("M14:GR605")

in the interests of personal taste and efficiency.

(3) I have changed Retail, Quick, Brand etc to lower case to accord with
your Select Case statement.

I suggest that you delete everthing in the sheet module and paste in the
following:.

Option Explicit
Public LastEnteredCell As Range

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range

Set LastEnteredCell = Target

For Each c In Target
If Not Intersect(c, Range("M14:GR605")) Is Nothing Then
Target.Cells.Interior.ColorIndex = xlNone
If IsNumeric(Target.Value) Then
If Target.Value 0 Then
Select Case LCase(Cells(Target.Row, 5).Text)
Case Is = "retail"
Target.Cells.Interior.ColorIndex = 38
Case Is = "quick"
Target.Cells.Interior.ColorIndex = 37
Case Is = "jewel"
Target.Cells.Interior.ColorIndex = 34
Case Is = "brand"
Target.Cells.Interior.ColorIndex = 36
Case Is = "other"
Target.Cells.Interior.ColorIndex = 2
Case Is = "generic"
Target.Cells.Interior.ColorIndex = 2

End Select
End If
End If
End If
Next c

End Sub
Sub goBack()
LastEnteredCell.Select
End Sub

The above worked for me.

---
Regards,
Norman



"BeSmart" wrote in message
...
Hi Norman
I haven't deleted the goBack sub I'm getting errors because of Option
Elicit...

First the code came up with an error of:
"Compile Error" Only Comments may appear after Sub End..." because after
the
Worksheet_Change code End Sub there was:

Option Elicit
Public LastEnteredCell As Range
--------------------------------------------------------------------------
Sub goBack()
LastEnteredCell.Select
End Sub

I understand this error because Option Elicit ... is outside a Sub / End
Sub.
So I got rid of Option Elicit (because it not really necessary????) and I
moved "Public LastEnteredCell As Range" below "Sub goBack()" but then I
got
this error and keep getting it no matter what I try:
"Object variable or With block variable not set"
================================================== ===
Sub goBack()
Public LastEnteredCell As Range
LastEnteredCell.Select
End Sub
================================================== ===
I know I need to add "With" or "For Each" something to the Sub goBack()
for
it to work, but I don't know where/what to put? (Sorry I'm a novice and
it
gets confusing)
Thanks for your help

Thanks Norman
That works brilliantly and I understand your points - especially about
Option Explicit (which I did spell correctly in the code)
Thanks for your help and patience.
BeSmart

"Norman Jones" wrote:

Hi BeSmart,

Firstly,

Option Elicit should be Option Explicit

Next,

So I got rid of Option Elicit (because it not really necessary????)

It is not *necessary* but it is highly desirable. For future reference, see
Chip Pearson's notes at:

http://www.cpearson.com/excel/variables.htm

I have made a couple of changes to your code:

(1) I have explicitly declared your c variable as a range object. The Option
Explicit statement at the head of your module requires all variables to be
declared.

(2) I have changed

[M14:GR605] to Range("M14:GR605")

in the interests of personal taste and efficiency.

(3) I have changed Retail, Quick, Brand etc to lower case to accord with
your Select Case statement.

I suggest that you delete everthing in the sheet module and paste in the
following:.

Option Explicit
Public LastEnteredCell As Range

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range

Set LastEnteredCell = Target

For Each c In Target
If Not Intersect(c, Range("M14:GR605")) Is Nothing Then
Target.Cells.Interior.ColorIndex = xlNone
If IsNumeric(Target.Value) Then
If Target.Value 0 Then
Select Case LCase(Cells(Target.Row, 5).Text)
Case Is = "retail"
Target.Cells.Interior.ColorIndex = 38
Case Is = "quick"
Target.Cells.Interior.ColorIndex = 37
Case Is = "jewel"
Target.Cells.Interior.ColorIndex = 34
Case Is = "brand"
Target.Cells.Interior.ColorIndex = 36
Case Is = "other"
Target.Cells.Interior.ColorIndex = 2
Case Is = "generic"
Target.Cells.Interior.ColorIndex = 2

End Select
End If
End If
End If
Next c

End Sub
Sub goBack()
LastEnteredCell.Select
End Sub

The above worked for me.

---
Regards,
Norman



"BeSmart" wrote in message
...
Hi Norman
I haven't deleted the goBack sub I'm getting errors because of Option
Elicit...

First the code came up with an error of:
"Compile Error" Only Comments may appear after Sub End..." because after
the
Worksheet_Change code End Sub there was:

Option Elicit
Public LastEnteredCell As Range
--------------------------------------------------------------------------
Sub goBack()
LastEnteredCell.Select
End Sub

I understand this error because Option Elicit ... is outside a Sub / End
Sub.
So I got rid of Option Elicit (because it not really necessary????) and I
moved "Public LastEnteredCell As Range" below "Sub goBack()" but then I
got
this error and keep getting it no matter what I try:
"Object variable or With block variable not set"
================================================== ===
Sub goBack()
Public LastEnteredCell As Range
LastEnteredCell.Select
End Sub
================================================== ===
I know I need to add "With" or "For Each" something to the Sub goBack()
for
it to work, but I don't know where/what to put? (Sorry I'm a novice and
it
gets confusing)
Thanks for your help