Update a range in a sheet click event (Excel 2003)

[JoAnn]

I have a sub in a module that defines a range based on user-entered start &
end rows. Those values are then combined & stored in a variable
(strClick_Range). All variables are declared at the beginning of the module
with DIM stmts. Both the module & the sheet are in the same workbook.

How can I have the newly defined range updated/used by my Sheet 1 click
event to ensure that it's always current? It generates an error ("Runtime
error: 1004 - method range of object Worksheet failed"), so I must be doing
something wrong.

Thanks,

 

[Per Jessen]

I have a sub in a module that defines a range based on user-entered start&
end rows. Those values are then combined & stored in a variable
(strClick_Range). All variables are declared at the beginning of the module
with DIM stmts.  Both the module & the sheet are in the same workbook.

How can I have the newly defined range updated/used by my Sheet 1 click
event to ensure that it's always current?  It generates an error ("Runtime
error: 1004 - method range of object Worksheet failed"), so I must be doing
something wrong.

Thanks,

Hi JoAnn

Let's see your code.

Regards,
Per

 

[JoAnn]

I stepped through the code & the range variable data isn't available to the
click event, which is causing the failure. I don't know how to get the range
I created in the subroutine to transfer over to the click event (I'm
relatively new to coding) ... here is the code.

The sub routine starts with all variables Dim as String, including the ones
used by the click event (but in the click event it says its a Variant & its
empty):

Dim strN_ClickStart As String
Dim strClick_Range As String


strR_Start = Application.InputBox("Enter STARTING Row for range (used LAST
FMTD ROW #): ")
strR_End = Application.InputBox("Enter ENDING Row for range: ")

'set up range for click event code
strN_ClickStart = "N4"
strN_EndName = "N" & strR_End
strClick_Range = strN_ClickStart & ":" & strN_EndName

'PLACE END OF RANGE MARKER
strMarker_Name = "S" & strR_End
Range(strMarker_Name).Select '<-- click event pops up here &
fails
ActiveCell.FormulaR1C1 = "*END"


<code after this, unhides hidden columns, copies formulas to added cells,
applies styles, etc.>


CLICK EVENT CODE:
Private Sub Worksheet_Change(ByVal Target As Range)
' cond fmt alternative to set row shading based on priority field value

Dim icolor As Integer

' If Not Intersect(Target, Range("N4:N900")) Is Nothing Then
If Not Intersect(Target, Range(strClick_Range)) Is Nothing Then '<--
this fails
Select Case Target
Case "Urgent"
icolor = 50 ' pale orange
Case "High"
icolor = 7 ' pink/flesh tone
Case Else
End Select

Target.EntireRow.Interior.ColorIndex = icolor ' ROW:
Target.EntireRow.Interior.ColorIndex = icolor
End If


Thanks for your help,JoAnn