Cell Formating in VLookup formula

When pulling data from a data range in VLookup formula is it possible for the
formatting of that data to transfer as well? ie. the color and font size.
Can a macro be written to accomplish this function?

ANy help would be appreciated.

A function can only return a result - it cannot bring the format from
another cell.

Yes, a macro could do this.

Hope this helps.

Pete

Pete,
How would i go about writing this Macro? Would I do it on the sheet tab
under "view code"? Or would it be under Tool - Macros - Record New Macros?
Please help.

"Pete_UK" wrote:

> A function can only return a result - it cannot bring the format from
> another cell.
>
> Yes, a macro could do this.
>
> Hope this helps.
>
> Pete
>
>

Mike Lindsay wrote...
>When pulling data from a data range in VLookup formula is it possible for the
>formatting of that data to transfer as well? ie. the color and font size.
>Can a macro be written to accomplish this function?

Someone else already told you functions can't return formats. They
can't return formats AS formats, i.e., they can't make the cell
containing the VLOOKUP formula have the same format as the cell VLOOKUP
finds. However, user-defined functions can return formatting
information as text, e.g., green 12 point.

If you want to do the former, then it'd be easiest if you changed your
VLOOKUP formulas into INDEX/MATCH formulas. In other words, change
formulas like

=VLOOKUP(a,b,c,d)

into

=INDEX(b,MATCH(a,INDEX(b,0,1),d),c)

The reason is that INDEX returns range references while VLOOKUP only
returns cell values. You need the range reference in addition to the
value.

Once you've changed the formulas, you could use either a Calculate
event handler in the Sheet class module for the worksheet containing
the VLOOKUP formula (right click on the worksheet tab and select View
Code from the pop-up menu) or you could use a SheetCalculate event
handler in the ThisWorkbook class module. A Calculate event handler
would look similar to

Private Sub Worksheet_Calculate()
Dim s As Range, r As Range
Application.EnableEvents = False
Set s = Range("C7")
Set r = Evaluate(Application.ConvertFormula(s.Formula, xlA1, xlA1,
1, s))
r.Copy
s.PasteSpecial xlPasteFormats
Application.EnableEvents = True
End Sub

where C7 contains the formula. More robust to name the cell(s)
containing the formula(s) for which you want to clone formatting, then
use the name in the event handler. The one above evaluates the formula
in cell C7, using the result to set a Range type object variable, which
should be the cell referenced by the formula in C7, then copies it and
pastes its format onto C7.

This isn't as robust as it appears, and if you try to do this for many
cells, you'll degrade apparent recalc speed noticeably.