Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If then Statement (Excel 2003)

    2 Columns:

    Col 1 is a dropdown:
    Choices: Red / Green / Yellow

    Col 2: Blank

    I need the code to output:

    If Col1 is Green, Format Col2 as RED(filled)

    if Col1 = Red or Yellow: input "n/a" in Col2.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If then Statement (Excel 2003)

    Right-click the sheet tab and select View Code from the popup menu.
    Create the following code in the module that is opened:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Range("A:A"), Target) Is Nothing Then
    For Each oCell In Intersect(Range("A:A"), Target)
    Select Case oCell
    Case ""
    oCell.Offset(0, 1).Interior.ColorIndex = xlColorIndexNone
    oCell.Offset(0, 1) = ""
    Case "Green"
    oCell.Offset(0, 1).Interior.Color = vbRed
    oCell.Offset(0, 1) = ""
    Case Else
    oCell.Offset(0, 1).Interior.ColorIndex = xlColorIndexNone
    oCell.Offset(0, 1) = "N/A"
    End Select
    Next oCell
    End If
    End Sub

    This assumes that the columns you mention are A and B. If they are different, modify the code accordingly.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If then Statement (Excel 2003)

    I will try it... Thanks HANS!!!!!

  4. #4
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If then Statement (Excel 2003)

    That worked... Awesome:

    Another dilema:

    When I open it, my Macro Security is set to High so it is Disabled. No problem, I selected Medium, where it asks me to "trust" it.

    How can I make this a "trusted" script to avoid the security issues? If I email this to 100 people, I will have to have them change the Security and I want to AVOID this...

    Thanks

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If then Statement (Excel 2003)

    You could sign the VBA code with a digital certificate. Each user will be asked once to trust the certificate. After that, the code will run even if macro security is set to High, without asking for macros to be enabled.

    However, digital certificates aren't cheap. You'll have to decide for yourself whether you (or your employer) can afford to get one.

    See About macro security - Excel for more info.

Posting Permissions

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