Results 1 to 2 of 2

Thread: Nested select else or if then VBA to change row cell coloring

  1. #1

    Nested select else or if then VBA to change row cell coloring



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am beyond lost here and hope someone can help.

    I have an Excel 2010 form. I am trying to change the row color based on several variables.
    I do understand that this can be accomplished with conditional formatting and have got that to work, but cutting and pasting, as my users will likely do, kills the formatting. I was hoping the VBA would fix that. Or possibly there is some other solution I am unaware of.

    This is what I want to happen (the so called logic)

    on Sheet3
    Columns (a w)
    rows (2 10485)
    upon a change in any field $x2 or a past due date in $T2

    if(AND($X2="Open",$T2<>"",$T2<=TODAY()) then all row red ($a2-$x2)
    if(AND($X2="Open",$T2="",$T2>TODAY()) then all row white ($a2-$x2)
    =$X2="Completed" then all row grey ($a2-$x2)
    =$X2="Rescinded" then $X2 = orange and $A2 thru $W2 = grey

    the x field will use a drop down and be either ( blank, open, completed, or rescinded )

    This is the code I have tried to hobble together and failed with.....


    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A2:X1048567")) Is Nothing Then Exit Sub
    Select Case Cells(Target.Row, "X").Value
    Case "Open"
    If Cells(Target.Row, "T").Value <> "" And T2 <= TODAY() Then 'Range(Cells(Target.Row, "A"), Cells(Target.Row, "F")).Interior.ColorIndex = 3
    Else
    Range(Cells(Target.Row, "A"), Cells(Target.Row, "F")).Interior.ColorIndex = x1None
    End Select

    Case "Completed"
    Range(Cells(Target.Row, "A"), Cells(Target.Row, "F")).Interior.ColorIndex = 15
    Case "Rescinded"
    Range(Cells(Target.Row, "A"), Cells(Target.Row, "F")).Interior.ColorIndex = 15
    Range(Cells(Target.Row, "A"), Cells(Target.Row, "F")).Interior.ColorIndex = 46
    Case ""
    Range(Cells(Target.Row, "A"), Cells(Target.Row, "F")).Interior.ColorIndex = xlNone
    End Select
    End Sub

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,512
    Articles
    0
    Excel Version
    365
    Check this hasn't already been satisfactorily answered at the cross post: http://stackoverflow.com/questions/2...in-select-case

    andeetercek, please read http://www.excelguru.ca/content.php?184 ; it's for your benefit.
    Last edited by p45cal; 2015-04-18 at 03:43 PM.

Posting Permissions

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