Pass Cell Value & Formatting to Another Worksheet

[Walter]

Worksheet1 has the following code, which works great! If I change the value
in any of the ranges then the format changes to the specified color from the
Case statement. I then want to pull the value and formatting of the cell
from the first worksheet to the next with it appearing in the correct row for
a particular company. Right now I can pull the data over by a simple cell
reference to the right worksheet cell but the formatting won't come over.
How do I get the formatting to pass over?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iColor As Integer

' Columns C thru G are named ranges.
' Whatever a user inputs as to a percentage, the cell will change to a
color based upon what is described in each Case below.
' For the particular range.

If Not (Intersect(Target, Me.Range("BESTDel")) Is Nothing) Then
Select Case Target.Value
Case Is < 0.9: iColor = 3
Case Is < 0.96: iColor = 6
Case Is < 0.98: iColor = 53
Case Is < 1: iColor = 16
Case Is = 1: iColor = 44
Case Else: iColor = xlNone
End Select
Target.Interior.ColorIndex = iColor


ElseIf Not (Intersect(Target, Me.Range("BESTQual")) Is Nothing) Then
Select Case Target.Value
Case Is < 0.98: iColor = 3
Case Is < 0.9955: iColor = 6
Case Is < 0.998: iColor = 53
Case Is < 1: iColor = 16
Case Is = 1: iColor = 44
Case Else: iColor = xlNone
End Select
Target.Interior.ColorIndex = iColor

ElseIf Not (Intersect(Target, Me.Range("SPMDel")) Is Nothing) Then
Select Case Target.Value
Case Is < 0.9: iColor = 3
Case Is < 0.96: iColor = 6
Case Is < 0.98: iColor = 53
Case Is < 1: iColor = 16
Case Is = 1: iColor = 44
Case Else: iColor = xlNone
End Select
Target.Interior.ColorIndex = iColor

ElseIf Not (Intersect(Target, Me.Range("SPMQual")) Is Nothing) Then
Select Case Target.Value
Case Is < 0.98: iColor = 3
Case Is < 0.9955: iColor = 6
Case Is < 0.998: iColor = 53
Case Is < 1: iColor = 16
Case Is = 1: iColor = 44
Case Else: iColor = xlNone
End Select
Target.Interior.ColorIndex = iColor

ElseIf Not (Intersect(Target, Me.Range("SPM12mo")) Is Nothing) Then
Select Case Target.Value
Case Is = "RED": iColor = 3
Case Is = "YLO": iColor = 6
Case Is = "BRZ": iColor = 53
Case Is = "SVR": iColor = 16
Case Is = "GLD": iColor = 44
Case Else: iColor = xlNone
End Select
Target.Interior.ColorIndex = iColor

End If
End Sub

Thanks!

 

[Dave Peterson]

First, you changed the code!

I'd still use "dim iColor as long" (forget that "as integer" exists!). And you
dropped the check for multiple cells. You should add that back or your
procedure will break when multiple cells are changed at once.

Secondly, if you used a formula to do that pulling, then that's the way formulas
work. They return values--not formatting.

Depending on how your data is laid out, maybe you could update to this
worksheet_change event?????