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

 

[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

 

[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