Hi Myles,

Just to add, you may, of course, pass a range which represents all the cells
on the sheet, e.g.:

Set c = sh.cells

---
Regards,
Norman



"Myles" wrote in
message ...

Hi Norman,
I was musing over what happened when I applied the Call idea to my
project.
Using your last tip-off, the structure of my coding becomes:

Sub MainCode()
Dim sh As Worksheet
Dim c As Range

For each sh in WorkSheets
For each c in sh.Range("a:a")

Call RecalculateRowScore(c)

Next
Next
End sub

Sub RecalculateRowScore(rng as Range)

If Instr(rng, "-")=0 Then
rng.Offset(0,1).value=Len(rng)*Asc(Mid(rng,1,1))
ElseIf Instr(rng, "-")0 Then
rng.Offset(0,1).value="-"
End If

<Other codes follow

End if
End Sub


The foregoing works perfectly (as does your cell colouring example).
However, rather than parse the cell c as argument, it appears
intuitively appealing to plump for parsing the worksheet object sh to
the servient subroutine. This option produces:

Sub MainCode2()
Dim sh As Worksheet
Dim c As Range

For each sh in WorkSheets

Call RecalculateRowScore2(sh)

Next
End sub

Sub RecalculateRowScore2(sh as Range) *' Object does not support this
property or method error* on this line
For each c in sh.Range("a:a")
If Instr(sh.rng, "-")=0 Then
sh.rng.Offset(0,1).value=Len(sh.rng)*Asc(Mid(sh.rn g,1,1))
ElseIf Instr(sh.rng, "-")0 Then
sh.rng.Offset(0,1).value="-"
End If

<Other codes follow

End if
End sub


Now the rub: why does EXCEL have a problem treating c any differently
from sh in the above examples?


myles


--
Myles
------------------------------------------------------------------------
Myles's Profile:
http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=496613