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!!!


--
peter_rivera
------------------------------------------------------------------------
peter_rivera's Profile: http://www.excelforum.com/member.php...o&userid=24495
View this thread: http://www.excelforum.com/showthread...hreadid=386180

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 ,
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!!

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 he

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