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!