auto copy cell format

Hello,

I have a complex spreadsheet in which I use cell formatting (shading) to
illustrate where one cell value is being copied elsewhere in the same sheet.
So for example I have a value 2.34 in cell C3 which is shaded pink, and
another cell G24 also 2.34 also shaded pink. It is helpful for display
purposes.

I can set G24 to 2.34 just by setting the cell '=C3'. But can I set it to
pink in a similar way?

I have Excell 2007.

I know how to copy a cell format using the format painter, no problem. But I
have several cell connections of this sort and an automated way of doing it
would be nice.

Thanks

KK

Install the following event macro in the worksheet code area:


Private Sub Worksheet_Change(ByVal Target As Range)

Dim t As Range, s As String, v As String, l As Long
Dim core As String
Dim FromWhere As Range

Set FromWhere = Nothing
Set t = Target
If Not t.HasFormula Then Exit Sub
v = t.Formula
l = Len(v) - 1
core = Right(v, l)
On Error Resume Next
Set FromWhere = Range(core)
If FromWhere Is Nothing Then Exit Sub
FromWhere.Copy
Application.EnableEvents = False
t.PasteSpecial Paste:=xlPasteFormats
Application.EnableEvents = True
End Sub

Once installed, if you enter a simple linking formula in a cell like:

=Z100

then the format of Z100 will be copied to the cell containing the formula.


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu200908


"KRK" wrote:

> Hello,
>
> I have a complex spreadsheet in which I use cell formatting (shading) to
> illustrate where one cell value is being copied elsewhere in the same sheet.
> So for example I have a value 2.34 in cell C3 which is shaded pink, and
> another cell G24 also 2.34 also shaded pink. It is helpful for display
> purposes.
>
> I can set G24 to 2.34 just by setting the cell '=C3'. But can I set it to
> pink in a similar way?
>
> I have Excell 2007.
>
> I know how to copy a cell format using the format painter, no problem. But I
> have several cell connections of this sort and an automated way of doing it
> would be nice.
>
> Thanks
>
> KK
>
> .
>