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 subprocedu

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