I'm missing something here....
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
|