How to preserve formatting with vlookup

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

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


--
Don Guillett
SalesAid Software
[email protected]
"Mathias" <[email protected]> wrote in message
news:[email protected]...
> 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??

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

"Don Guillett" wrote:

> 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
>
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Mathias" <[email protected]> wrote in message
> news:[email protected]...
> > 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??
>
>
>