Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Changed cell color based on IF statement (Excel xp)

    I want to change a cell color based on a drop down box i've created in excel that has 5 choices. I made an IF statement in the formula but don't know how to tell it to make the cell a certain color given what it sees? Is this possible, i tried conditional formatting but have more than 3 conditions so i can't use it. Thank you for the help.

  2. #2
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changed cell color based on IF statement (Excel xp)

    This is off the top of my head and is a little cludgy but it works.
    I linked the combo box to a cell. A small VBA routine looks at the cell value and changes the target cells interior color dependant upon the drop down selection.
    Attached Files Attached Files

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Changed cell color based on IF statement (Excel xp)

    Why not try Conditional Formatting (under the Format menu) it will respond to values or IF statements.
    Might be easier than a VBA solution.

    cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Changed cell color based on IF statement (Excel xp)

    but how do i make each IF statement trigger a different color? if i put 5 if statements in one condition, how can i ask for different shades of green based on each seperate IF condition? thanks

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Changed cell color based on IF statement (Excel xp)

    VB is needed since Conditional formatting ALLOWS only 4 conditions/colors, and the request was for 5.

    Steve

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Changed cell color based on IF statement (Excel xp)

    thank you for this. is works great. i have 50 of these that the user will choose their option and then the cell next to it will turn that color.

    How can i change your macro to be "relative" so i don't have to make 50 of them with C13, C14, C15 etc.? thanks for the help

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changed cell color based on IF statement (Excel xp)

    hmmmm....let me play around with this at home and i will get back to you...but knowing this board, there will be several others who will have an answer for you before i can respond. what i love about this place is the different approaches / solutions to a given problem that a query will receive.

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Changed cell color based on IF statement (Excel xp)

    my sentiments exactly! thanks for the help

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changed cell color based on IF statement (Excel xp)

    How about this using a combination of Data Valadation and the Worksheet Change event.
    Attached Files Attached Files
    Legare Coleman

  10. #10
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changed cell color based on IF statement (Excel xp)

    Once again it aint pretty but it works. Time consuming to configure all the controls and tweaking the code. I only did 10 rows.
    Attached Files Attached Files

  11. #11
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changed cell color based on IF statement (Excel xp)

    I didnt look at Legares solution till I was done. His is much prettier than mine.

  12. #12
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Changed cell color based on IF statement (Excel xp)

    Good catch - I wasn't reading closely.
    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  13. #13
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Changed cell color based on IF statement (Excel xp)

    Thank you for this code. Could you tell me how to prevent the user from entering numbers on rows 20, 22, and 24. Also, the user can only enter 1 thru 6 and if they enter anything else i'd like to have a message box tell them to enter the right number. Here is the code you helped me with. Thank you for the help.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range

    With ActiveSheet
    If Intersect(Target, Union(.Range("C20:C75"), .Range("E20:E75"), .Range("G20:G75"), _
    .Range("I20:I75"), .Range("K20:K75"), .Range("M20:M75"), .Range("O20:O75"), .Range("Q20:Q75"), _
    .Range("S20:S75"), .Range("U20:U75"), .Range("W20:W75"), .Range("Y20:Y75"))) Is Nothing Then Exit Sub
    For Each oCell In Intersect(Target, Union(.Range("C20:C75"), .Range("E20:E75"), _
    .Range("G20:G75"), .Range("I20:I75"), .Range("K20:K75"), .Range("M20:M75"), .Range("O20:O75"), .Range("Q20:Q75"), _
    .Range("S20:S75"), .Range("U20:U75"), .Range("W20:W75"), .Range("Y20:Y75")))
    With oCell.Interior
    Select Case oCell.Value
    Case 1
    .ColorIndex = 38
    oCell.Font.ColorIndex = 38
    Case 2
    .ColorIndex = 40
    oCell.Font.ColorIndex = 40
    Case 3
    .ColorIndex = 36
    oCell.Font.ColorIndex = 36
    Case 4
    .ColorIndex = 5
    oCell.Font.ColorIndex = 5
    Case 5
    .ColorIndex = 37
    oCell.Font.ColorIndex = 37
    Case 6
    .ColorIndex = 16
    oCell.Font.ColorIndex = 16
    Case Else
    .ColorIndex = xlColorIndexNone
    oCell.Font.ColorIndex = xlColorIndexAutomatic
    End Select
    End With
    Next oCell
    End With

    End Sub
    Attached Files Attached Files

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changed cell color based on IF statement (Excel xp)

    You can use Worksheet protection to keep the user from entering anything in rows 20, 22, and 24. Select all of the cells on the worksheet by clicking the little button at the top of the row numbers. Select Cells from the Format menu and then click on the protection tab. Now remove the check mark next to Locked. Click OK to close the dialog box. Now select the cells you want to protect (all of rows 20, 22, and 24 or just the cells in those rows in the relevatn columns). Go back to Format/Cells protection tab and turn on the check mark next to Locked. Now click on Protection on the Tools menu and select Protect Worksheet from the pop-up menu. Assign a password if you want one and click ok. The user should now not be able to enter into any of the cells that you locked.

    The code below adds a MsgBox to the Else part of the Select Case statement to display a message if the value is not between 1 and 6 and then delete the value from the cell.

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    With ActiveSheet
    If Intersect(Target, Union(.Range("C20:C75"), .Range("E20:E75"), .Range("G20:G75"), _
    .Range("I20:I75"), .Range("K20:K75"), .Range("M20:M75"), .Range("O20:O75"), .Range("Q20:Q75"), _
    .Range("S20:S75"), .Range("U20:U75"), .Range("W20:W75"), .Range("Y20:Y75"))) Is Nothing Then Exit Sub
    For Each oCell In Intersect(Target, Union(.Range("C20:C75"), .Range("E20:E75"), _
    .Range("G20:G75"), .Range("I20:I75"), .Range("K20:K75"), .Range("M20:M75"), .Range("O20:O75"), .Range("Q20:Q75"), _
    .Range("S20:S75"), .Range("U20:U75"), .Range("W20:W75"), .Range("Y20:Y75")))
    With oCell.Interior
    Select Case oCell.Value
    Case 1
    .ColorIndex = 38
    oCell.Font.ColorIndex = 38
    Case 2
    .ColorIndex = 40
    oCell.Font.ColorIndex = 40
    Case 3
    .ColorIndex = 36
    oCell.Font.ColorIndex = 36
    Case 4
    .ColorIndex = 5
    oCell.Font.ColorIndex = 5
    Case 5
    .ColorIndex = 37
    oCell.Font.ColorIndex = 37
    Case 6
    .ColorIndex = 16
    oCell.Font.ColorIndex = 16
    Case Else
    .ColorIndex = xlColorIndexNone
    oCell.Font.ColorIndex = xlColorIndexAutomatic
    MsgBox "The value in cell " & oCell.Address(False, False) & "is not between 1 and 6. It has been removed."
    oCell.Value = ""
    End Select
    End With
    Next oCell
    End With
    End Sub
    </pre>

    Legare Coleman

  15. #15
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Changed cell color based on IF statement (Excel xp)

    once again thank you for your help. The protection worked well. I added the CASE ELSE, and i get a runtime '1004", unable to set the colorindex property of the interior class. i don't know what that means.
    Attached Files Attached Files

Page 1 of 3 123 LastLast

Posting Permissions

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