Vlookup and Formatting

P

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 formul
that shows the data presented on Sheet 1, but without the formatting.

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

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



peter_rivera
 
Top