Thanks
I understand the error message now.


"Rick Rothstein" wrote in message
...
'Calling code

xlSht.Cells(1, 1).Font = NameFormatType1(255)

Private Function NameFormatType1(NmCol As Long) As Excel.Font
NameFormatType1.Color = RGB(NmCol, 0, 0)
NameFormatType1.Bold = True
End Function

There are two problems with your originally posted code... one easily
solved, the second not. First, in your calling line of code, Font is an
object, so you must use the Set keyword to assign your function's returned
object to it. So that calling line should have looked like this...

Set xlSht.Cells(1, 1).Font = NameFormatType1(255)

Doing that, however, reveals the second problem... your function is
declared as an object, but it has no reference. That is, it's a font
object, but a font object to what? Obviously, you want it to reference A1
on xlSht, but how is it to know that? Before you can change properties of
NameFormatType1, you first have to Set it to some object. In this case,
you would need the equivalent of this...

Set NameFormatType1 = xlSht.Cells(1, 1)

so it would know what object is having its properties modified. Of course,
having to do this would interfere with the approach you are trying to use.

--
Rick (MVP - Excel)


"Rod" wrote in message
...
OK, that works fine. Thanks

Out of curiosity can my way be made to work.
It compiled OK so I was hopeful.



"joel" wrote in message
...

Try these changes


Sub test()
Set xlSht = ActiveSheet
Call NameFormatType1(xlSht.Cells(1, 1), 255)

End Sub


Private Sub NameFormatType1(ByRef cell As Range, _
ByVal NmCol As Long)

With cell.Font
Color = RGB(0, 0, NmCol)
Bold = True
End With

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=166121

Microsoft Office Help