You cannot do that with a function (formula).
A function cannot change the formatting of any cell including itself.

If you only have three colors you might be able to do something
with Conditional Formatting based on value.

You can write a macro to color a cell based on such things as
value, or relative position to another cell with the color you want,
or something that would be able to do manually. If all you have
is an assignment to another cell you could run a macro to look at the
formula of a cell within a selection and copy the format from the
other cell.

Here are a couple of macros that you can try on a copy of your workbook.
that appear to work for simple assignment statements.

** In this case, I expect feedback, whether it works or not **
I will be adding these to my colors.htm page.

Instructions for installing and using macros in
http://www.mvps.org/dmcritchie/excel/getstarted.htm

The first macro will only copy the only the interior formatting (pattern color).
The second will copy all formatting.

Sub ColorOfAssignment()
Selection.Interior.ColorIndex = xlAutomatic 'clear color
Dim rng As Range, cell As Range
Set rng = Selection
For Each cell In Intersect(rng, _
rng.SpecialCells(xlFormulas))
On Error Resume Next
cell.Interior.ColorIndex = _
Range(Mid(cell.Formula, 2)).Interior.ColorIndex
On Error GoTo 0
Next cell
End Sub

Sub FormatOfAssignment()
Dim rng As Range, cell As Range
Set rng = Selection
For Each cell In Intersect(rng, _
rng.SpecialCells(xlFormulas))
On Error GoTo passby
Range(Mid(cell.Formula, 2)).Copy
cell.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
passby:
On Error GoTo 0
Next cell
End Sub

testing: ----------------
ONLY tested for within the SAME worksheet.
A1: 'A1-1 (pattern color red)
A2: 'A2-1 (pattern color blue)
A3: 'A3-1 (pattern color yellow)

B1: =A3
B2: =A1
B3: =A2

also tested for
=Sheet4!A18
='Sheet four'!A18
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Vamshee" wrote in message ...
I have given you a simple example. My actual work involves gathering
information from multiple spreadsheets in the same excel document using
vlookup. In the example I have mentioned before and the vlookup, I only get
the data and not the format info of the cell.

Considering the amount of data that is interlinked, I dont want to do it
manually. I mean copying the format information manually is not a feasible
solution. I looking for a function that will not only copy the data but also
the format.

Thank you for your help.
Vamshee




"David McRitchie" wrote:

A function cannot change the formatting of any cell including itself.

You can gain color formatting and borders by using Conditional
Formatting, but you are not copying formatting from another cell.

You would need to use a macro, or copy manually.

To copy only the formatting manually
Copy the source selection
to paste only the formatting
Edit, Paste Special, paste formats.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Vamshee" wrote in message ...
Let me explain my issue with an example.

Lets say I have a link to a website , for example http://www.microsoft.com/
in cell A1 which is formatted in a specific font and color and is also a
hyperlink. When I want to have the same information in cell A1 in other cells
say C3 and B5, I use =A1 in the cells C3 and B5.

But what happens is that only the information http://www.microsoft.com is
copied to cells C3 and B5 and not the format of the text or the hyperlink.

How can I have the format also to be copied to cells C3 and B5 when
referencing.

Thanks in advance.


Vamshee