Use same format as another cell

[Rick Jones]

I use the Offset function in a cell to show the value of a cell from another
sheet. I would like cell with the Offset funtion to not only show the value
but also have the same format as the cell it references - in particular the
cell shading. Can I do this?

Thanks - Rick

 

[Don Guillett]

IMO you would need an event macro to copy/paste the format.
More specifics needed.

 

[Gord Dibben]

Rick

If the source cell is shaded due to Conditional Formatting use the same CF on
the target cell.

If source cell is manually formatted, the target cell with the formula can
only return the results of the formula, not the formatting of the source cell.

Gord Dibben XL2002

 

[Rick Jones]

Thanks for the responses.

Conditional formating would work except that I have about 7 different colors
that the cell shading can be. I found some macros on David McRitchie's web
site that may be applicable but was hoping for a non-macro solution. Looks
like a macro is the way to go.

Thanks again,
Rick

 

[Don Guillett]

Here's an example
Use a select case macro that is automatic. Modify to suit
right click sheet tab>view code>insert this>SAVE

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 4 And Target.Column = 1 Then
Select Case Target.Value
Case "A"
Target.Interior.ColorIndex = 5
Case "S"
Target.Interior.ColorIndex = 6
Case "C"
Target.Interior.ColorIndex = 7
Case "I"
Target.Interior.ColorIndex = 8
Case Else
Target.Interior.ColorIndex = 0
End Select
End If
End Sub

 

[Rick Jones]

Thanks Don I'll give it a shot - Rick

Here's an example
Use a select case macro that is automatic. Modify to suit
right click sheet tab>view code>insert this>SAVE

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 4 And Target.Column = 1 Then
Select Case Target.Value
Case "A"
Target.Interior.ColorIndex = 5
Case "S"
Target.Interior.ColorIndex = 6
Case "C"
Target.Interior.ColorIndex = 7
Case "I"
Target.Interior.ColorIndex = 8
Case Else
Target.Interior.ColorIndex = 0
End Select
End If
End Sub

 

[Rick Jones]

Don,

Gave your code a try. Works fine if I manually change the cell. I need
the color to change when I change the cell that is referenced using the
Offset function (This referenced cell is on another worksheet). Changing
the referenced cell does not trigger the Worksheet_Change event on the
worksheet that I want the color to change. Any way I can force the
Worksheet_Change event to occur for the worksheet that has the cell that
contains the Offset function?

Thanks again,
Rick