Passing range to subprocedure - maybe?

[Mike Gerbracht]

I'm using a macro that calls a subprocedure (the subprocedure colors
the passed range yellow). When I do a test, the subprocedure DOES
work fine. But in my full macro, the subprocedure DOESN'T work. The
full macro DOES work if I use the variable.interior.colorindex=6
command, which is essentially all the subprocedure does anyway.
(Eventually, the color subprocedure will get more complicated, so I do
need to do it through its own procedure.) Somehow, passing the ranges
to the subprocedure doesn't work, even though the subprocedure works
on its own.

So I'm stumped. Here's the relevant code:

Thanks in advance,
Mike


The subprocedure:

Sub color(RangeToColor As Range)
RangeToColor.Interior.ColorIndex = 6
End Sub

The test:

Sub testcolor()
Dim testrng As Range
Set testrng = Range("c1:c10")
color testrng
End Sub

The problem:

Sub NewMacro()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'
Dim str As String
Dim rng As Range
Dim nrng As Range
Dim currentrow As Range
'
Dim cono, jobno, famlvl, famcode As Range
' actually many more variables here
'
MaxRow = Cells.SpecialCells(xlLastCell).Row
MaxCol = Cells.SpecialCells(xlLastCell).Column
'
'Delete old range names (to avoid duplication)
For Each nm In ThisWorkbook.Names
nm.Delete
Next

'now rename each column as a named range with the name being the title
_
in the first row concatenated with "range" (i.e. conorange)
j = 1
Do While j <= MaxCol
Set rng = Cells(1, j)
Set nrng = rng.Resize(MaxRow, 1)
str = Cells(1, j).Value & "range"
Names.Add str, nrng
j = j + 1
Loop
'
i = 2
Do While i <= MaxRow
Set currentrow = Cells(i, 1).EntireRow
'define each cell to be checked as the intersect of the variable
column and the current row
Set cono = Intersect(currentrow, Range("conorange"))
Set jobno = Intersect(currentrow, Range("jobnorange"))
Set famlvl = Intersect(currentrow, Range("famlvlrange"))
Set famcode = Intersect(currentrow, Range("famcoderange"))
'
'many more
'
'now specific checks
'works this way
If (IsEmpty(cono) Or Not (Application.IsNumber(cono.Value))) Then
_
cono.Interior.ColorIndex = 6
If (IsEmpty(jobno) Or Not (Application.IsNumber(jobno.Value)))
Then _
jobno.Interior.ColorIndex = 6
If (IsEmpty(famlvl) Or Not (Application.IsNumber(famlvl.Value)))
Then _
famlvl.Interior.ColorIndex = 6
If (IsEmpty(famcode) Or Not (Application.IsNumber(famcode.Value)))
Then _
famcode.Interior.ColorIndex = 6
'doesn't work this way
If (IsEmpty(cono) Or Not (Application.IsNumber(cono.Value))) Then
_
color cono
If (IsEmpty(jobno) Or Not (Application.IsNumber(jobno.Value)))
Then _
color jobno
If (IsEmpty(famlvl) Or Not (Application.IsNumber(famlvl.Value)))
Then _
color famlvl
If (IsEmpty(famcode) Or Not (Application.IsNumber(famcode.Value)))
Then _
color famcode
'
'etc
'
i = i + 1
Loop

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic 'XL97 & later
End Sub

 

[Bob Kilmer]

Mike,
Well, perhaps predictably, the tester works for me. I don't really want to
try to implement the longer example. Have you stepped through the code from
NewMacro into color and back? What is the value of RangeToColor in color?
Can you address RangeToColor while the code is stopped in color (e.g., in
Immediate window, issue 'Application.Goto RangeToColor' or RangeToColor =
"X")?

Note that by using 'Dim cono, jobno, famlvl, famcode As Range' only famcode
will be a true Range data type. The others will be Variants. Try declaring
the "range" variables explicitly as Ranges.

 

[Bob Kilmer]

Another observation. You appear to be implicitly using the ActiveSheet, in
as much as I do not see that you specify a particular worksheet for your
ranges. As a rule, I tend to specify the workbook/worksheet if I think there
may be a hint of ambiguity. This may not be contributing to your problem in
this case, but it is worth a mention.