3

I need a custom function to lookup both a cell value and copy the source format. I have a list of values in two columns. The first is of integers and the second column is the corresponding text values. For example:

A B
--- ----------
1 My first value
2 My second value
3 My third value

I want to lookup the value I provide as it corresponds to those in column A. Based on the row number of the cell found with the matching value in column A, it will select the corresponding value in column B. This is easy enough with the LOOKUP() function in Excel. However, I want to ALSO copy the text formatting of the cell in column B.

Initially I approached this using a VBA function but unfortunately a function cannot change the formatting of a cell. Would appreciate ideas on how to approach.

Improve this question
1
  • You want to lookup a string in column A and return the value and formatting of the corresponding value in column B? Return it to where? And where is the value that you're looking up?
    – Raystafarian
    Feb 27, 2012 at 19:14

2 Answers 2

Reset to default
1 
Sub test()
    On Error GoTo Suberror
    'turn off screen updating
    Application.ScreenUpdating = False
    'ask for input
    strName = InputBox(Prompt:="Lookup Value", _
              Title:="Lookup Value", Default:="0")
        'if no input - exit
        If strName = "0" Or _
               strName = vbNullString Then

               Exit Sub
        'otherwise Find
        Else

            Columns("A:A").Select
            Selection.Find(What:=strName, After:=ActiveCell, _
            LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, _
            SearchFormat:=False).Activate

            'Copy
            ActiveCell.Offset(0, 1).Copy

        End If

        'Paste to the range that you define
            Range("J1").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:= _
                xlNone, SkipBlanks:=False, Transpose:=False
            Selection.PasteSpecial Paste:=xlPasteFormats, Operation:= _
                xlNone, SkipBlanks:=False, Transpose:=False
Suberror:
    Range("A1").Select
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

    End Sub
Improve this answer
6
  • Thanks for providing this Raystafarian! The intention was to use this as a function such as =LookupWithFormatting(A1,IDSource,TextSource). The parameters in this example are identical to the MSExcel =LOOKUP() function. That's how I was hoping to approach this. Thoughts?
    – BigBrother
    Feb 27, 2012 at 20:34
  • @BigBrother lookup will only return a value, not formatting
    – Raystafarian
    Feb 27, 2012 at 20:41
  • Yep - therein is what started this whole pain point. I started a solution by writing a custom VBA function that accepted the three parameters as =LOOKUP(...) does. In the VBA function code I call LOOKUP(...) AND I had hoped to use code to copy formatting as well but it didn't work. I discovered MS Excel VBA functions cannot modify formatting. Any ideas?
    – BigBrother
    Feb 27, 2012 at 20:43
  • My idea was the VBA code above. It will take your input, look it up and copy the other value (and formatting) to your defined range. I read it as VBA solution, not a worksheet function solution
    – Raystafarian
    Feb 27, 2012 at 20:50
  • Thanks for taking the shot! Do you think its not possible to do this as a function?
    – BigBrother
    Feb 27, 2012 at 21:06
0

In general, functions entered in a cell cannot change the formatting of the cell they are in because the function does not know which cell it is in. The only way around that is to pass the address of the current cell into the function.

Function LookupWithFormatting(rValue As Range, rLookup As Range, rResult As Range, rFormatThisCell As Range) As String

The function would be entered in cell B1 as =LookupWithFormatting(A1, IDSource, TextSource, B1).

If you don't have many formats, you might consider using Conditional Formatting instead of writing a VBA function. (You would need a separate conditional format for each value in your lookup table.)

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .