Consulting

Results 1 to 7 of 7

Thread: Solved: Formulas, Code, and my ignorance....

  1. 10-24-2008, 01:51 AM #1
    GoKats78
    GoKats78 is offline
    VBAX Regular
    Joined
    Oct 2008
    Posts
    69
    Location

    Solved: Formulas, Code, and my ignorance....

    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,"Receive d",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
    Reply With Quote Reply With Quote
  2. 10-24-2008, 02:03 AM #2
    mdmackillop
    mdmackillop is offline
    Administrator
    VP-Knowledge Base     VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Add another Select level to your event code and remove the If statements

    [vba]
    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.
    [/vba]
    Last edited by mdmackillop; 10-24-2008 at 02:07 AM. Reason: Call line added
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.

    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'
    Reply With Quote Reply With Quote
  3. 10-24-2008, 03:10 AM #3
    GoKats78
    GoKats78 is offline
    VBAX Regular
    Joined
    Oct 2008
    Posts
    69
    Location
    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
    Last edited by GoKats78; 10-24-2008 at 03:35 AM.
    Reply With Quote Reply With Quote
  4. 10-24-2008, 05:34 AM #4
    mdmackillop
    mdmackillop is offline
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Etc. just means continue your coding.

    You need to step through your code to identify the specific error. I can't do that.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.

    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'
    Reply With Quote Reply With Quote
  5. 10-27-2008, 11:40 AM #5
    GoKats78
    GoKats78 is offline
    VBAX Regular
    Joined
    Oct 2008
    Posts
    69
    Location
    This did it... Thanks.



    [VBA]
    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
    [/VBA]
    Reply With Quote Reply With Quote
  6. 10-27-2008, 11:45 AM #6
    mdmackillop
    mdmackillop is offline
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Glad you're fixed. FYI, Use the green VBA button to format code as shown [code ] doesn't do the formatting.
    Regards
    MD
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.

    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'
    Reply With Quote Reply With Quote
  7. 10-27-2008, 02:25 PM #7
    GoKats78
    GoKats78 is offline
    VBAX Regular
    Joined
    Oct 2008
    Posts
    69
    Location
    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...
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules