Pick up format from another cell

I can make a function in a cell that picks up the content from another
cell - example =A3

However; this will only show the content, and not the format from A3.

How can I make a link that picks up BOTH the content + the format. (If the
content in A3 is blue; I want it to show in blue in the cell where =A3
stands). Is this possible??

Kathrine

Formulas and Functions can return result only.

They cannot change or copy formatting.

If the content of A3 is blue due to Conditional Formatting, you can apply the
same CF to the target cell.


Gord Dibben MS Excel MVP

On Thu, 15 Jun 2006 23:19:28 +0200, "Kathrine J Wathne" <[email protected]>
wrote:

>I can make a function in a cell that picks up the content from another
>cell - example =A3
>
>However; this will only show the content, and not the format from A3.
>
>How can I make a link that picks up BOTH the content + the format. (If the
>content in A3 is blue; I want it to show in blue in the cell where =A3
>stands). Is this possible??
>

OK. Thanks for helping - any other way to make this possible - not using a
function but something else?


"Gord Dibben" <gorddibbATshawDOTca> skrev i melding
news:[email protected]...
> Kathrine
>
> Formulas and Functions can return result only.
>
> They cannot change or copy formatting.
>
> If the content of A3 is blue due to Conditional Formatting, you can apply
> the
> same CF to the target cell.
>
>
> Gord Dibben MS Excel MVP
>
> On Thu, 15 Jun 2006 23:19:28 +0200, "Kathrine J Wathne" <[email protected]>
> wrote:
>
>>I can make a function in a cell that picks up the content from another
>>cell - example =A3
>>
>>However; this will only show the content, and not the format from A3.
>>
>>How can I make a link that picks up BOTH the content + the format. (If the
>>content in A3 is blue; I want it to show in blue in the cell where =A3
>>stands). Is this possible??
>>
>

Formulas can't do this kind of thing. They return values to the cell with the
formula.



Kathrine J Wathne wrote:
>
> I can make a function in a cell that picks up the content from another
> cell - example =A3
>
> However; this will only show the content, and not the format from A3.
>
> How can I make a link that picks up BOTH the content + the format. (If the
> content in A3 is blue; I want it to show in blue in the cell where =A3
> stands). Is this possible??

--

Dave Peterson

How does A3 change?

If it changes because the user typed something, you could use an event macro
that would copy that cell to the other cell.

Rightclick on the worksheet tab and select View code. Paste this in the new
code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

'only one cell at a time!
If Target.Cells.Count > 1 Then Exit Sub

If Intersect(Target, Me.Range("a3")) Is Nothing Then Exit Sub

Application.EnableEvents = False
Target.Copy _
Destination:=Me.Range("a5") 'some range
Application.EnableEvents = True

End Sub

Be aware that changing the format won't fire this event. You really have to
change the cell (or hit F2|Enter to pretend to change the cell).


Kathrine J Wathne wrote:
>
> OK. Thanks for helping - any other way to make this possible - not using a
> function but something else?
>
> "Gord Dibben" <gorddibbATshawDOTca> skrev i melding
> news:[email protected]...
> > Kathrine
> >
> > Formulas and Functions can return result only.
> >
> > They cannot change or copy formatting.
> >
> > If the content of A3 is blue due to Conditional Formatting, you can apply
> > the
> > same CF to the target cell.
> >
> >
> > Gord Dibben MS Excel MVP
> >
> > On Thu, 15 Jun 2006 23:19:28 +0200, "Kathrine J Wathne" <[email protected]>
> > wrote:
> >
> >>I can make a function in a cell that picks up the content from another
> >>cell - example =A3
> >>
> >>However; this will only show the content, and not the format from A3.
> >>
> >>How can I make a link that picks up BOTH the content + the format. (If the
> >>content in A3 is blue; I want it to show in blue in the cell where =A3
> >>stands). Is this possible??
> >>
> >

--

Dave Peterson

Let's say that cell C10 has the formula =A3 and we desire that whenever cell
A3 changes format, cell C10 will automatically do the same. Enter the
following in worksheet code:

Dim ping As Boolean
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("A3")) Is Nothing Then
If ping = False Then
Range("A3").Copy
Range("C10").PasteSpecial Paste:=xlPasteFormats
End If
ping = True
Exit Sub
Else
ping = False
End If
End Sub


Move to cell A3 (mouse or arrow keys). Change its format. Then move away
from A3. C10 will automatically pick up the new format.

REMEMBER: worksheet code !
--
Gary's Student


"Kathrine J Wathne" wrote:

> I can make a function in a cell that picks up the content from another
> cell - example =A3
>
> However; this will only show the content, and not the format from A3.
>
> How can I make a link that picks up BOTH the content + the format. (If the
> content in A3 is blue; I want it to show in blue in the cell where =A3
> stands). Is this possible??
>
>
>