Copying Formatting Between Linked Cells In Excel ’97

by on December 12, 2007

Q: I have a Microsoft Excel ’97 question. If I am linking a cell to another cell, and I want the color from the first cell to be carried over what do I do?

A: Microsoft Excel is a great spreadsheet program. It has many wonderful built in features that make working with numbers very easy. Your question is two fold; you want to link two cells together, and carry the formatting from one to the other. Is this possible? Yes, but it requires some programming in Visual Basic for Applications (VBA). Rather than go to all of this trouble, I suggest another approach: using the Format Painter. (I know, this won’t copy the formatting automatically, but it is the next best thing in my opinion.)

First, link your cells as you normally would. Next select the “source” cell. Click the Format Painter button on the Standard toolbar. Select the second linked cell. Simple as that, all of your formatting will be copied to the second cell.

So you still want to go down the VBA road? Here is a sample portion of VBA code that you can use to copy your formatting automatically between two linked cells:

Here is the original VBA code (code borrowed from allexperts.com) in which font colors and formatting are carried between the two cells:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next
If Target.Value = “” Then Target.Interior.ColorIndex = 0
If Target.Value = “A” Or Target.Value = “a” Then Target.Interior.ColorIndex = 3 ‘ Red
If Target.Value = “W” Or Target.Value = “w” Then Target.Interior.ColorIndex = 6 ‘ Yellow
If Target.Value = “X” Or Target.Value = “x” Then Target.Interior.ColorIndex = 4 ‘ Green
If Target.Value = “N” Or Target.Value = “n” Then Target.Interior.ColorIndex = 33 ‘ Blue
If Target.Value = “25%” Or Target.Value = “25” Then Target.Interior.ColorIndex = 43 ‘Light Green
If Target.Value = “50%” Or Target.Value = “50” Then Target.Interior.ColorIndex = 50 ‘Medium Green
If Target.Value = “75%” Or Target.Value = “75” Then Target.Interior.ColorIndex = 10 ‘Dark Green
If Target.Value = vbNullString Then Target.Interior.ColorIndex = 0

End Sub

If you want to carry cell borders and shading between the two cells, you will also need to do the following:

The following procedure must be saved in Sheet1 of book1
(in VBE, project explorer of Book1 (left-handside of the screen), Click sheet1)
Sub Worksheet_Change(ByVal w1 As Range)
MsgBox “aa”
Dim w2 As Range
Set w2 = Workbooks(“book2.xls”).Sheets(1).Range(“a1”)
With w2
.Formula = w1.Formula
With .Interior
.ColorIndex = w1.Range(“a1”).Interior.ColorIndex
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End With
End Sub

You can go rounds with VBA code if you are new to it, or just don’t know it very well, so I leave this as a challenged for you to tackle if you so choose. I on the other hand, am slightly lazy and would simply stick to using the Format Painter, which is not so automatic, but a wonderful tool nonetheless.

I hope this information helps! Good luck!