How to keep font color when referencing Sheets in the same workboo

[AdirondackSam]

In the same workbook (Office 2007):

In Sheet 2, in Cell A1 I have the word "Michigan" in red font.

In Sheet 1 at Cell B3 I want to reference the above cell "=Sheet1!A1"

Michigan is displayed according to play, but the font color changes to the
default black. Is there any way to keep the color attribute???

 

[Barb Reinhardt]

If the font color was set with conditional formatting, you can format the
second sheet/cell the same way. If you want it to match, you'll need to do
something with VBA, although I'm not exactly sure how I'd approach it.

 

[AdirondackSam]

Thank you Barb, but conditional formatting is not used.

This is simple input using a RED color on the font as described in the
original post. I'm just trying to carry the font color over to another
spreadsheet by reference.

 

[Gord Dibben]

Cannot be done without some type of code, macro or event.

BTW..........your example formula has a typo

"=Sheet1!A1" should be =Sheet2!A1 or you have your sheets mixed up

Here is a macro you could run on Sheet1 assuming it has the linking
formulas.

Sub format_link_cells()
Dim myCell As Range
For Each myCell In Selection
If myCell.Value = Sheets("Sheet2").Range(myCell.Address).Value Then
myCell.Interior.ColorIndex = Sheets("Sheet2") _
.Range(myCell.Address).Interior.ColorIndex
Else: myCell.Interior.ColorIndex = xlNone
End If
Next myCell
End Sub


Gord Dibben MS Excel MVP

 

[AdirondackSam]

Gord:

Correct you are on the error and than you for pointing that out. I will try
your Macro and see if I can get it to work, but you at least confirmed what I
suspected --- there's no easy simple way to carry the formatting over from
one sheet to another. So at least I can stop searching the 'help' files and
banging my head against a wall.

Thanks!!!!