can formatting be linked in Excel?

[ruthcali]

I'm using Excel 2000.

i have 2 spreadsheets that are linked.

so in one spreadsheet, called test2.xls, cell A6 has the formula : =[test1.xls]Sheet1!$A$6

If i go to test1.xls and make cell A6 bold and yellow, i want test2.xls cell A6 to also have the same formatting.

can formatting be linked?

Thanks,

 

[ruthcali]

Does anyone have any ideas?

Thanks,

 

[WildHare]

This is not simple to do because of the separate files.

I could find no automatic way to do this, so I imagine that the only way may be a 'brute force' attack - use a function to return the cell formatting of a particular range, and then call that function for every cell in your destination range and apply the formatting.

It would probably be about 30 lines of VBA code, if you feel up to it.

For example, "ActiveCell.Font.Color" will return the foreground color of a cell....there are numerous properties of a cell/range that you can investigate and use later on.

JMH







If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[xlbo]

not really - no event is fired when formatting is changed - ergo you would have to loop through all cells in the worksheet and apply the formatting.

Hang on a mo - you could use pastespecial>formats but you would have to do it on the selection change event - this would take a second or so as you would have to do a pastespecial for all cells at once to avoid the loop - this would get VERY irritating very quickly as it would fire whenever you moved cells in excel

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[ruthcali]

Thanks to both of you for writing.

it will be enough to make the code fire once, like when the spreadsheet first opens. I'm familiar with Access, but does Excel have an OnOpen event?

So the user will update test1.xls.

When he is done, he opens test2.xls and the code fires and all the formatting gets changed there as well.

Thanks,

 

[WildHare]

There is a worksheet activate event, a worksheet change event, and a workbook open event, and a bunch more. Just check 'em out...

JMH

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[ruthcali]

Thanks. it works!!

i made a command button on the test2.xls spreadsheet and typed the following code:

Sub Button1_Click()

Windows("test1.xls&quot.Activate
Range("A:Z&quot.Select
Application.CutCopyMode = False
Selection.Copy
Windows("test2.xls&quot.Activate
Range("A:Z&quot.Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False