Solved: How to Invoke a Formatting Macro

[Cyberdude]

I have a macro that does nothing but insert a value into a cell then format it. I use it to save lines of code in other macros. The following is an example of the calling sequence and a simplified version of the formatting macro:

?Calling sequence:
Range("D" & CurrRow).Select
Call RunMessageDescrip("<-- '05 High", 12, Green, xlLeft, FNm:="Arial", RowHT:=17.3)

?Formatting macro:
Sub RunMessageDescrip(Msg$, Optional FSiz = 12, Optional FColr = Red, Optional HAlgn = xlCenter, _
Optional BoldVal = True, Optional FNm = "Arial", Optional RowHT = 16.9, Optional ItalicVal = False)
With Selection
With .Font
.Size = FSiz
.Name = FNm
.Bold = BoldVal
End With
.HorizontalAlignment = HAlgn
.Value = Msg
End With
End Sub 'RunMessageDescrip' I want to get rid of the Range("D" & CurrRow).Select statement in the calling sequence and use something else like With Range(?D? & CurrRow). But I can?t figure out how to do it. The With Selection statement in the called macro requires that a cell be selected, so I have to remove that statement. But what do I use to relate the code (like ?With .Font?) in the called macro to the calling sequence?

[Zack Barresse]

Pass the range through another variable in your procedure. The below has not been tested by myself but should steer you in the right direction..

?Calling sequence:
Call RunMessageDescrip(Range("D" & CurrRow), "<-- '05 High", 12, Green, xlLeft, FNm:="Arial", RowHT:=17.3)

?Formatting macro:
Sub RunMessageDescrip(Target As Range, Msg$, Optional FSiz = 12, Optional FColr = Red, Optional HAlgn = xlCenter, _
Optional BoldVal = True, Optional FNm = "Arial", Optional RowHT = 16.9, Optional ItalicVal = False)
With Target
With .Font
.Size = FSiz
.Name = FNm
.Bold = BoldVal
End With
.HorizontalAlignment = HAlgn
.Value = Msg
End With
End Sub 'RunMessageDescrip'

[Cyberdude]

Looks good, Zack! I'll give it a try. Thanx for the reply. http://vbaexpress.com/forum/images/smilies/039.gif