PDA

View Full Version : Solved: Formulas, Code, and my ignorance....



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

mdmackillop
10-24-2008, 02:03 AM
Add another Select level to your event code and remove the If statements


Private Sub worksheet_change(ByVal target As Range)
If target.Count > 1 Then Exit Sub
select case target.columm
case 32
Select Case target.Value
Case "Open": target.Offset(0, 1).Interior.ColorIndex = 3
Case "Received": target.Offset(0, 1).Interior.ColorIndex = 6
'etc.
end select
case 2
Call FMT
etc.

GoKats78
10-24-2008, 03:10 AM
I must be missing something...that doesn't work.

What does the second "etc" mean? I understand the first...


Below is what I have...it errors out

I get a message "Object doesn't support this property or method"



Private Sub worksheet_change(ByVal target As Range)
If target.Count > 1 Then Exit Sub
Select Case target.columm
Case 32
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

Case 2
Call Fmt

mdmackillop
10-24-2008, 05:34 AM
Etc. just means continue your coding.

You need to step through your code to identify the specific error. I can't do that.

GoKats78
10-27-2008, 11:40 AM
This did it... Thanks.




Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(target, Range("c2809:c6000,x2809:x6000,ae2809:ae6000")) Is Nothing Then
Dim LR As Long, c As Range
LR = Range("AG" & Rows.Count).End(xlUp).Row
For Each c In Range("AG2810:AG" & LR)
Select Case c.Value
Case "Open": c.Offset(0, 0).Interior.ColorIndex = 3
Case "Received": c.Offset(0, 0).Interior.ColorIndex = 6
Case "Closed": c.Offset(0, 0).Interior.ColorIndex = 4
Case "Forced Closed": c.Offset(0, 0).Interior.ColorIndex = 1: c.Offset(0, 0).Font.ColorIndex = 2
Case "Void": c.Offset(0, 0).Interior.ColorIndex = 48
Case Else: c.Offset(0, 0).Interior.ColorIndex = xlNone: c.Offset(0, 0).Font.ColorIndex = xlAutomatic
End Select
Next c
end Sub

mdmackillop
10-27-2008, 11:45 AM
Glad you're fixed. FYI, Use the green VBA button to format code as shown [code ] doesn't do the formatting.
Regards
MD

GoKats78
10-27-2008, 02:25 PM
Thanks again...Before last week I had never even attempted to write a single line of code....I really don't have a clue what I am doing...I'm just faking it...