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

auto copy cell format
 

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

.

auto copy cell format
 

Have you considered precedent/dependent arrows

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

--
Matt Lynn

Message posted via http://www.officekb.com

auto copy cell format
 

Can you use Conditional Formatting on source cell C3 to show pink?

Then use same CF on G24


Gord Dibben MS Excel MVP

On Fri, 23 Oct 2009 12:51:53 +0100, "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

auto copy cell format - Thanks
 

Thanks, there are some good ideas here.

K


"KRK" wrote in message
...
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

error
 

So I tried this on a worksheet and I got an error.... run time error '13' Type mismatch... which brought me to v = t.Formula where I guess the error began.

I'm new using visual basic, so if you could tell me what is wrong that would be great!




Gary''s Student wrote:

Install the following event macro in the worksheet code area:Private Sub
23-Oct-09

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:

Previous Posts In This Thread:

On Friday, October 23, 2009 7:51 AM
KRK wrote:

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

On Friday, October 23, 2009 9:28 AM
Gary''s Student wrote:

Install the following event macro in the worksheet code area:Private Sub
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:

On Friday, October 23, 2009 9:31 AM
Mattlynn via OfficeKB.com wrote:

Have you considered precedent/dependent arrowsKRK wrote:--Matt LynnMessage
Have you considered precedent/dependent arrows

KRK wrote:

--
Matt Lynn

Message posted via http://www.officekb.com

On Friday, October 23, 2009 10:47 AM
Gord Dibben wrote:

Can you use Conditional Formatting on source cell C3 to show pink?
Can you use Conditional Formatting on source cell C3 to show pink?

Then use same CF on G24


Gord Dibben MS Excel MVP

On Saturday, October 24, 2009 7:04 AM
KRK wrote:

Thanks, there are some good ideas here.K
Thanks, there are some good ideas here.

K

EggHeadCafe - Software Developer Portal of Choice
Delegates to the Event
http://www.eggheadcafe.com/tutorials...the-event.aspx

error
 

One. You should stay in the ORIGINAL thread. I just tested the code and it
worked properly. Did you copy into a SHEET module

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Carisa Ventura" wrote in message
...
So I tried this on a worksheet and I got an error.... run time error '13'
Type mismatch... which brought me to v = t.Formula where I guess the error
began.

I'm new using visual basic, so if you could tell me what is wrong that
would be great!




Gary''s Student wrote:

Install the following event macro in the worksheet code area:Private Sub
23-Oct-09

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:

Previous Posts In This Thread:

On Friday, October 23, 2009 7:51 AM
KRK wrote:

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

On Friday, October 23, 2009 9:28 AM
Gary''s Student wrote:

Install the following event macro in the worksheet code area:Private Sub
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:

On Friday, October 23, 2009 9:31 AM
Mattlynn via OfficeKB.com wrote:

Have you considered precedent/dependent arrowsKRK wrote:--Matt LynnMessage
Have you considered precedent/dependent arrows

KRK wrote:

--
Matt Lynn

Message posted via http://www.officekb.com

On Friday, October 23, 2009 10:47 AM
Gord Dibben wrote:

Can you use Conditional Formatting on source cell C3 to show pink?
Can you use Conditional Formatting on source cell C3 to show pink?

Then use same CF on G24


Gord Dibben MS Excel MVP

On Saturday, October 24, 2009 7:04 AM
KRK wrote:

Thanks, there are some good ideas here.K
Thanks, there are some good ideas here.

K

EggHeadCafe - Software Developer Portal of Choice
Delegates to the Event
http://www.eggheadcafe.com/tutorials...the-event.aspx