Hi Hafeez,

Public Sub formatWorkbook(ByRef XL As Excel.Application,
ByVal strRangeName As String, _
ByRef FormatRange As Excel.Range)
'Copies format of named range to selection
XL.Goto (strRangeName)
If (Selection.Row = FormatRange.Row) Then
Exit Sub
End If

sht.Cells(1, 1).Select
XL.Goto (strRangeName)
Selection.Copy
FormatRange.Select
FormatRange.PasteSpecial xlPasteFormats, , False, False
XL.ActiveSheet.Cells(1, 1).Select
End Sub

Specifically at "Selection.Row"

Whenever you use any of Excel's 'global' objects, such as Cells,
Selection etc, you must prefix them with the instance of Excel you're
using:

Public Sub formatWorkbook(ByRef XL As Excel.Application,
ByVal strRangeName As String, _
ByRef FormatRange As Excel.Range)
'Copies format of named range to selection
XL.Goto (strRangeName)
If (XL.Selection.Row = FormatRange.Row) Then
Exit Sub
End If

sht.Cells(1, 1).Select
XL.Goto (strRangeName)
XL.Selection.Copy
FormatRange.Select
FormatRange.PasteSpecial xlPasteFormats, , False, False
XL.ActiveSheet.Cells(1, 1).Select
End Sub



Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie