How to preserve formatting with vlookup

[Guest]

Hi!
I use vlookup to fetch text from cells on a separate sheet and it works just
fine. The problem though is that I want to get the format of the fetched text
also e.g. If the text in all cells is blue on sheet2 and I want to fetch it
with vlookup to sheet1, then I only get the text and the text is not blue!
The same thing is if the text is in italics, then vlookup stills return the
text i normal font. Is there a way to make vlookup return the format of the
fetched cell??

 

[Don Guillett]

try

Sub findwithformat()
With Sheets("sheet5").Columns(1)
x = .Find("xxxx").Address
..Range(x).Copy
Sheets("sheet9").Range("h1") _
..PasteSpecial Paste:=xlPasteAll
End With
End Sub

 

[Guest]

Hello again!
Thanks for your reply!

Another problem occured when I finally solved the problem namely that when I
copy text in a cell that has been formatted it looses it's formatting if you
paste it in a cell that has been merged! Is there a workaround for that? I've
solved the latter problem also, simply by implementing my own
vlookup-function and then merge the cells after I paste the values! Kinda
complex problem though, because I also want to adjust the rowheight in the
merged cells, so all text is visible...