Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Formating in VLookup formula
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Formating in VLookup formula
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Formating in VLookup formula
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions | |||
VLOOKUP formula appears in the cell I need to see the result in | Excel Discussion (Misc queries) |