Formating Functions

  • Thread starter Thread starter don walden
  • Start date Start date
D

don walden

I created a function to lookup a cell in another
workbook. The formula returns the correct information but
does not return any formating. I need to be able to bring
in the color of the text and the cell color. How do I do
this?
 
You cannot do this with a formula.

You would have to copy/paste:

either manually or

with a VBA procedure.

David Hager
Excel FMVP
 
Don,

Worksheet formulas can only return values. To copy properties like cell
formatting, you need a macro like:

'*****
Sub test()
Dim sourceRange As Range, destRange As Range
Set sourceRange = Workbooks("Book1").Sheets("Sheet1").Range("D6")
Set destRange = Workbooks("Book2").Sheets("Sheet1").Range("C3")
With destRange
.Value = sourceRange.Value
.Interior.Color = sourceRange.Interior.Color
.Font.Color = sourceRange.Font.Color
End With
End Sub
'*****

HTH
Anders Silvén
 
Back
Top