GoKats78
10-24-2008, 01:51 AM
I will start by saying I am certainly sure there is a better way to do all this...but I have what I have...
I need to find a way to run some code when data is entered
for instance ..
If I enter a date in Column C I need Column AF and AG to change color... I have a formula written in AF to display certain things depending on what column had data entered in...
for instance - when we first create the entry (a date issued is entered in Column C - the status is "open" , when we receive the response (data entered in the "date received column" - Column X ) the status is "received", after we accepted the response we enter the date in Column AE - that changes the status to "Closed".... There is also a "Forced Closed" status...
I can't use conditional formatting as Excel 2003 only has 3 levels of formatting and I have more than that....
My formula works....=IF(C3085>0,IF(W3085>0,"Forced Closed",IF(SUM(X3085+AE3085)>X3085,"Closed",IF(SUM(X3085+AE3085)>0,"Received",IF(ISBLANK(AE3085),"Open")))),"")
What doesn't work is the color do not change (based on the code) unless I manually copy and paste the status into the status cell (simply CRTL-C then CRTL-V)
I have a macro written to make the changes - what I need is to get the macro to fire when data is entered in Column C, Column X, and/or Column AE
The code below is what I have... the blue changes the colors, the red is the section I need to fire automatically when data is entered,,,
It works (the code in red) if I go to Macros-run macro. But I need it to run automatically...
Private Sub worksheet_change(ByVal target As Range)
If target.Count > 1 Then Exit Sub
If target.Column = 32 Then
Select Case target.Value
Case "Open": target.Offset(0, 1).Interior.ColorIndex = 3
Case "Received": target.Offset(0, 1).Interior.ColorIndex = 6
Case "Closed": target.Offset(0, 1).Interior.ColorIndex = 4
Case "Forced Closed": target.Offset(0, 1).Interior.ColorIndex = 1: target.Offset(0, 1).Font.ColorIndex = 2
Case "Void": target.Offset(0, 1).Interior.ColorIndex = 48
Case Else: target.Offset(0, 1).Interior.ColorIndex = xlNone: target.Offset(0, 1).Font.ColorIndex = xlAutomatic
End Select
End If
If target.Count > 1 Then Exit Sub
If target.Column = 32 Then
Select Case target.Value
Case "Open": target.Interior.ColorIndex = 3
Case "Received": target.Interior.ColorIndex = 6
Case "Closed": target.Interior.ColorIndex = 4
Case "Forced Closed": target.Interior.ColorIndex = 1: target.Font.ColorIndex = 2
Case "Void": target.Interior.ColorIndex = 48
Case Else: target.Interior.ColorIndex = xlNone: target.Offset(0, 0).Font.ColorIndex = xlAutomatic
End Select
End If
If target.Count > 1 Then Exit Sub
If target.Column = 2 Then
Select Case target.Value
Case "MP": target.Offset(0, -1).Interior.ColorIndex = 3
Case "Warr": target.Offset(0, -1).Interior.ColorIndex = 45
Case "NM": target.Offset(0, -1).Interior.ColorIndex = 38
Case "Info": target.Offset(0, -1).Interior.ColorIndex = 5: target.Offset(0, -1).Font.ColorIndex = 2
Case "Void": target.Offset(0, -1).Interior.ColorIndex = 48
Case Else: target.Offset(0, -1).Interior.ColorIndex = xlNone: target.Offset(0, -1).Font.ColorIndex = xlAutomatic
End Select
End If
If target.Count > 1 Then Exit Sub
If target.Column = 2 Then
Select Case target.Value
Case "MP": target.Interior.ColorIndex = 3
Case "Warr": target.Interior.ColorIndex = 45
Case "NM": target.Interior.ColorIndex = 38
Case "Info": target.Interior.ColorIndex = 5: target.Font.ColorIndex = 2
Case "Void": target.Interior.ColorIndex = 48
Case Else: target.Interior.ColorIndex = xlNone: target.Interior.Font.ColorIndex = xlAutomatic
End Select
End If
End Sub
Sub Fmt()
Dim LR As Long, c As Range
LR = Range("Af" & Rows.Count).End(xlUp).Row
For Each c In Range("Af2810:Af" & LR)
Select Case c.Value
Case "Open": c.Offset(0, 1).Interior.ColorIndex = 3
Case "Received": c.Offset(0, 1).Interior.ColorIndex = 6
Case "Closed": c.Offset(0, 1).Interior.ColorIndex = 4
Case "Forced Closed": c.Offset(0, 0).Interior.ColorIndex = 1: c.Offset(0, 1).Font.ColorIndex = 2
Case "Void": c.Offset(0, 1).Interior.ColorIndex = 48
Case Else: c.Offset(0, 1).Interior.ColorIndex = xlNone: c.Offset(0, 1).Font.ColorIndex = xlAutomatic
End Select
Next c
End Sub
I need to find a way to run some code when data is entered
for instance ..
If I enter a date in Column C I need Column AF and AG to change color... I have a formula written in AF to display certain things depending on what column had data entered in...
for instance - when we first create the entry (a date issued is entered in Column C - the status is "open" , when we receive the response (data entered in the "date received column" - Column X ) the status is "received", after we accepted the response we enter the date in Column AE - that changes the status to "Closed".... There is also a "Forced Closed" status...
I can't use conditional formatting as Excel 2003 only has 3 levels of formatting and I have more than that....
My formula works....=IF(C3085>0,IF(W3085>0,"Forced Closed",IF(SUM(X3085+AE3085)>X3085,"Closed",IF(SUM(X3085+AE3085)>0,"Received",IF(ISBLANK(AE3085),"Open")))),"")
What doesn't work is the color do not change (based on the code) unless I manually copy and paste the status into the status cell (simply CRTL-C then CRTL-V)
I have a macro written to make the changes - what I need is to get the macro to fire when data is entered in Column C, Column X, and/or Column AE
The code below is what I have... the blue changes the colors, the red is the section I need to fire automatically when data is entered,,,
It works (the code in red) if I go to Macros-run macro. But I need it to run automatically...
Private Sub worksheet_change(ByVal target As Range)
If target.Count > 1 Then Exit Sub
If target.Column = 32 Then
Select Case target.Value
Case "Open": target.Offset(0, 1).Interior.ColorIndex = 3
Case "Received": target.Offset(0, 1).Interior.ColorIndex = 6
Case "Closed": target.Offset(0, 1).Interior.ColorIndex = 4
Case "Forced Closed": target.Offset(0, 1).Interior.ColorIndex = 1: target.Offset(0, 1).Font.ColorIndex = 2
Case "Void": target.Offset(0, 1).Interior.ColorIndex = 48
Case Else: target.Offset(0, 1).Interior.ColorIndex = xlNone: target.Offset(0, 1).Font.ColorIndex = xlAutomatic
End Select
End If
If target.Count > 1 Then Exit Sub
If target.Column = 32 Then
Select Case target.Value
Case "Open": target.Interior.ColorIndex = 3
Case "Received": target.Interior.ColorIndex = 6
Case "Closed": target.Interior.ColorIndex = 4
Case "Forced Closed": target.Interior.ColorIndex = 1: target.Font.ColorIndex = 2
Case "Void": target.Interior.ColorIndex = 48
Case Else: target.Interior.ColorIndex = xlNone: target.Offset(0, 0).Font.ColorIndex = xlAutomatic
End Select
End If
If target.Count > 1 Then Exit Sub
If target.Column = 2 Then
Select Case target.Value
Case "MP": target.Offset(0, -1).Interior.ColorIndex = 3
Case "Warr": target.Offset(0, -1).Interior.ColorIndex = 45
Case "NM": target.Offset(0, -1).Interior.ColorIndex = 38
Case "Info": target.Offset(0, -1).Interior.ColorIndex = 5: target.Offset(0, -1).Font.ColorIndex = 2
Case "Void": target.Offset(0, -1).Interior.ColorIndex = 48
Case Else: target.Offset(0, -1).Interior.ColorIndex = xlNone: target.Offset(0, -1).Font.ColorIndex = xlAutomatic
End Select
End If
If target.Count > 1 Then Exit Sub
If target.Column = 2 Then
Select Case target.Value
Case "MP": target.Interior.ColorIndex = 3
Case "Warr": target.Interior.ColorIndex = 45
Case "NM": target.Interior.ColorIndex = 38
Case "Info": target.Interior.ColorIndex = 5: target.Font.ColorIndex = 2
Case "Void": target.Interior.ColorIndex = 48
Case Else: target.Interior.ColorIndex = xlNone: target.Interior.Font.ColorIndex = xlAutomatic
End Select
End If
End Sub
Sub Fmt()
Dim LR As Long, c As Range
LR = Range("Af" & Rows.Count).End(xlUp).Row
For Each c In Range("Af2810:Af" & LR)
Select Case c.Value
Case "Open": c.Offset(0, 1).Interior.ColorIndex = 3
Case "Received": c.Offset(0, 1).Interior.ColorIndex = 6
Case "Closed": c.Offset(0, 1).Interior.ColorIndex = 4
Case "Forced Closed": c.Offset(0, 0).Interior.ColorIndex = 1: c.Offset(0, 1).Font.ColorIndex = 2
Case "Void": c.Offset(0, 1).Interior.ColorIndex = 48
Case Else: c.Offset(0, 1).Interior.ColorIndex = xlNone: c.Offset(0, 1).Font.ColorIndex = xlAutomatic
End Select
Next c
End Sub