auto copy cell format

K

KRK

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
 
G

Gary''s Student

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
 
G

Gord Dibben

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

Then use same CF on G24


Gord Dibben MS Excel MVP
 
C

Carisa Ventura

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 Lon
Dim core As Strin
Dim FromWhere As Rang

Set FromWhere = Nothin
Set t = Targe
If Not t.HasFormula Then Exit Su
v = t.Formul
l = Len(v) -
core = Right(v, l
On Error Resume Nex
Set FromWhere = Range(core
If FromWhere Is Nothing Then Exit Su
FromWhere.Cop
Application.EnableEvents = Fals
t.PasteSpecial Paste:=xlPasteFormat
Application.EnableEvents = Tru
End Su

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

=Z10

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 windo
2. select View Code - this brings up a VBE windo
3. paste the stuff in and close the VBE windo

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

To remove the macro

1. bring up the VBE windows as abov
2. clear the code ou
3. close the VBE windo

To learn more about macros in general, see

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

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

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

-
Gary''s Student - gsnu20090

:

Previous Posts In This Thread:

auto copy cell format
Hello

I have a complex spreadsheet in which I use cell formatting (shading) t
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, an
another cell G24 also 2.34 also shaded pink. It is helpful for displa
purposes

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

I have Excell 2007

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

Thank

KK

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 Lon
Dim core As Strin
Dim FromWhere As Rang

Set FromWhere = Nothin
Set t = Targe
If Not t.HasFormula Then Exit Su
v = t.Formul
l = Len(v) -
core = Right(v, l
On Error Resume Nex
Set FromWhere = Range(core
If FromWhere Is Nothing Then Exit Su
FromWhere.Cop
Application.EnableEvents = Fals
t.PasteSpecial Paste:=xlPasteFormat
Application.EnableEvents = Tru
End Su

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

=Z10

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 windo
2. select View Code - this brings up a VBE windo
3. paste the stuff in and close the VBE windo

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

To remove the macro

1. bring up the VBE windows as abov
2. clear the code ou
3. close the VBE windo

To learn more about macros in general, see

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

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

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

-
Gary''s Student - gsnu20090

:

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

KRK wrote:

--
Matt Lynn



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

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/tutorial...810a-6cb6fed2ca82/delegates-to-the-event.aspx
 
D

Don Guillett

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
(e-mail address removed)
in message
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Remove Hyperlinks Without Changing background shading 14
Conditional Formatting Question. 3
Copy cell to a new cell 2
Copy current color of conditional formatted cell 4
Conditional Format & Formula 2
Conditional Formatting A Cell Based on Another Cell's Value 1
Multiple cell conditional formatting using the IF 3
In-cell progress bars? 7

Top