| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass Cell Value & Formatting to Another Worksheet
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pass Cell Value & Formatting to Another Worksheet
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????? Walter wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using Conditional Formatting to Pass/Fail | Excel Discussion (Misc queries) | |||
| Conditional Formatting - Getting pass the 3 condition limit | Excel Discussion (Misc queries) | |||
| Conditional Formatting - Getting pass the 3 condition limit | Excel Discussion (Misc queries) | |||
| pass worksheet name to a macro | Excel Programming | |||
| Pass worksheet to macro as a parameter | Excel Programming | |||
Linear Mode
