1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel:Additional Conditional Formatting

Discussion in 'Business Applications' started by flipinoluv, Apr 21, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. flipinoluv

    flipinoluv Thread Starter

    Joined:
    Apr 16, 2004
    Messages:
    21
    I notice that you are limited to only 3 conditional formats. Is there a way to add more?

    Thanks
     
  2. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    There's a workround for 6 font colours. For >3 fill colours you have to use code. Which are you after?

    Rgds,
    Andy
     
  3. flipinoluv

    flipinoluv Thread Starter

    Joined:
    Apr 16, 2004
    Messages:
    21
    Yes, that is correct. I am looking for code that would include formatting for a minimum of 4 colors. Thanks.
     
  4. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    That was an either/or question. Since you said "I am looking for code" I'll assume you're dealing with fill colours, not font colours.

    You need to describe what you're trying to do, then we might be able to help.

    Rgds,
    Andy
     
  5. flipinoluv

    flipinoluv Thread Starter

    Joined:
    Apr 16, 2004
    Messages:
    21
    Ha, sorry about the confusion.
    I am looking for fill colors, although font colors might not be a good thing to know as well.
    I am trying to create a drop down list for every cell in a worksheet. I plan on having the user select text from a list, and then have the conditional formatting to change font color as well as the fill color to be the same color, essentially making the box appear as one color.
    Unfortunately, excel only allows me have 3 conditional formats and I need a minimum of 4.
    Thanks.
     
  6. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    OK. MustBNuts says Select Case is best for this, but since I haven't figured that out yet ...

    Let's say your validation (list) options are Red, Blue, Green, Yellow.

    Rightclick the sheet tab and View Code. Paste this in -

    --------------------------------------------------------------
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = "Red" Then
    Selection.Interior.ColorIndex = 3
    Selection.Font.ColorIndex = 3
    ElseIf Target = "Blue" Then
    Selection.Interior.ColorIndex = 5
    Selection.Font.ColorIndex = 5
    ElseIf Target = "Green" Then
    Selection.Interior.ColorIndex = 10
    Selection.Font.ColorIndex = 10
    ElseIf Target = "Yellow" Then
    Selection.Interior.ColorIndex = 6
    Selection.Font.ColorIndex = 6
    ElseIf Target = "" Then
    Selection.Interior.ColorIndex = 0
    Selection.Font.ColorIndex = 0
    End If
    End Sub
    --------------------------------------------------------------

    Close the Visual Basic Editor, then test the validation.

    Rgds,
    Andy
     
  7. flipinoluv

    flipinoluv Thread Starter

    Joined:
    Apr 16, 2004
    Messages:
    21
    Thanks, I think I can work with this. I appreciate your help.
     
  8. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    You're welcome. :)

    Rgds,
    Andy
     
  9. flipinoluv

    flipinoluv Thread Starter

    Joined:
    Apr 16, 2004
    Messages:
    21
    OK. Here is the issue.
    I need the current cell to change the colors.

    What is happening is that after I type in, "Red" it turns the next cell i select to Red. I am not sure why this is happening.
     
  10. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> after I type in, "Red" it turns the next cell i select to Red

    Yeah, that's your fault (for saying "I plan on having the user select text from a list" in the first place). :p ( ;))

    Change your statements to

    If Target = "Red" Then
    Target.Interior.ColorIndex = 3
    Target.Font.ColorIndex = 3

    Rgds,
    Andy
     
  11. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    In the Select Case (used in vba) it's really easy, you are simply replacing your IF with a Case and encapsulating it with a variable to look at and the end select:

    'anything after a single quote mark is a comment that will not interfere with code
    Select Case Target

    Case "Red"
    Target.Interior.ColorIndex = 3
    Target.Font.ColorIndex = 3

    Case "Blue"
    Target.Interior.ColorIndex = 5
    Target.Font.ColorIndex = 5

    Case Else
    msgbox "Your choice is not available", vbokonly

    end select


    This is the structure, and you can add as many "cases" as you have situations and add as many instruction code lines in those cases as you need. Try to ensure that you always have a Case Else just for the oddball user who defies logic! :D

    It is just a bit easier than an If Then Else because you do away with the elseif scenario and deal only with the if (case).

    Hope this helps,

    MBN
     
  12. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> It is just a bit easier than an If Then Else

    Yes. Works just the same as, but is infinitely more followable. Exactly as you said before ; if only my skull wasn't impenetrable.

    This is an excellent example ; I'm stealing it.

    Thanks,
    Andy
     
  13. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    Where would you like me to send the bill?! :D:D:D:D
     
  14. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    You choose.

    Case 1
    "Bill Gate$"

    Case 2
    "My eldest (owes me a fortune.)"

    :D :D
     
  15. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    Tsk, tsk, tsk....how quickly they forget....and I thought I'd taught you better than that....


    Where is your Case Else?

    ...and BTW, that's what kids and pets are for (so they say), they eat you out of house and home, use up all your resources and claim that the payback is emotional fulfillment! ...but at least you can put the dog in a kennel when you want to go on vacation! :D:D

    MBN
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/222636

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice