I also re-named "A1" " MyVlookup" and that did not make a difference either.
I appreciate the time you have commited to my problem.

Thanks A lot


"Mike Lindsay" wrote:

Richard,
I did exactly as you suggested and I cut & Pasted the follow into the Visual
Basic Editor:

Sub TestCopyFormat()
Columns("C:C").Find(what:=Range("A1").Text, after:=Range("C1")).Copy
Range("MyVlookup").PasteSpecial (xlPasteFormats)
End Sub

It still did not change the formatting in A1. Would you object to e-mailing
me the sample spread you created?

"Richard Buttrey" wrote:

OK let's double check.

A1: =VLOOKUP(2,B:C,2)
B1: 1
B2: 2
B3: 3
C1: Test1
C2: Test2
C3 Test3

Now format C2 with some distinctive pattern and/or font and run the
macro. In my test workbook it changes A1 to the same format as C2.

Post back if you're still having probs.

Rgds


On Thu, 4 May 2006 11:13:01 -0700, Mike Lindsay
wrote:

Richard Thank You for the suggestion but it does not appear to work. I set
up a spread sheet as you used in your example. W/ the VLookup in cell "A1"
and the data table encompasing information in Column "C". I re-named cell
A1 "MY VLookup". I put the code you sent into the VBA, but while I did not
recieve an erro message the Cell w/ the VLookup formula (A1) did not change
formatting to match the information in cell C1. Any further suggestions?

"Richard Buttrey" wrote:

On Thu, 4 May 2006 06:33:01 -0700, Mike Lindsay
wrote:

How do I write a macro or a formula that allows me to not just copy the data
in V-Lookup function but the way the data is formatted as well. My Sample
formula is as follows: =VLOOKUP(B2,Sheet2!A1:C4,2). Is there a way to
re-write this formula to copy the formatting as well or possible a macro can
be written?

Appreciations to anyone who can answer this question .


I suppose it depends on how many of these formulae you have.

As a general example assuming just one Vlookup in cell A1which is
named "MyVlookup", and with the value that is a result of the look up
in column C of a table, you could put the following in a VBA
procedure.

Sub TestCopyFormat()
Columns("C:C").Find(what:=Range("A1").Text, after:=Range("C1")).Copy
Range("MyVlookup").PasteSpecial (xlPasteFormats)
End Sub

If there are several vlookups you could no doubt adapt this and build
in a looping procedure.

HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________