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