Vlookup and Formatting

[peter_rivera]

On Sheet1, I have data with cells that have different formatting (e.g. different text colors/bold/etc.). On Sheet 2, I have a vlookup formula that shows the data presented on Sheet 1, but without the formatting.

Is it possible while using a vlookup formula to display both the data and the data's original formatting? If not, how else can I accomplish this? Thanks!!!

[JE McGimpsey]

Worksheet functions return values to their calling cells. They can't
return formats.

You could do the lookup in a VBA Event procedure to return both:

Private Sub Worksheet_Calculate()
'Assume lookup value is in A1, value and format
'to be returned in B1
'VLOOKUP equivalent: =VLOOKUP(A1,Sheet2!A:B,2,FALSE)
Dim rFound As Range
With Worksheets("Sheet2").Range("A:A")
Set rFound = .Find( _
What:=Range("A1").Value, _
After:=.Cells(.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With
Application.EnableEvents = False
With Range("B1")
If rFound Is Nothing Then
.Value = CVErr(xlErrNA)
.ClearFormats
Else
rFound.Offset(0, 1).Copy
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlFormats
End If
End With
Application.EnableEvents = True
End Sub



In article <[email protected]>,
peter_rivera
<[email protected]> wrote:

> On Sheet1, I have data with cells that have different formatting (e.g.
> different text colors/bold/etc.). On Sheet 2, I have a vlookup formula
> that shows the data presented on Sheet 1, but without the formatting.
>
> Is it possible while using a vlookup formula to display both the data
> and the data's original formatting? If not, how else can I accomplish
> this? Thanks!!

[Debra Dalgleish]

No, a VLookup formula can only return a value, not the formatting from
the original cell.

Perhaps you could use conditional formatting to format the cells, based
on the value returned. There are instructions in Excel's Help, and here:

http://www.contextures.com/xlCondFormat01.html

peter_rivera wrote:
> On Sheet1, I have data with cells that have different formatting (e.g.
> different text colors/bold/etc.). On Sheet 2, I have a vlookup formula
> that shows the data presented on Sheet 1, but without the formatting.
>
> Is it possible while using a vlookup formula to display both the data
> and the data's original formatting? If not, how else can I accomplish
> this? Thanks!!!
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html