Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Mar 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    more then 3 conditions with conditional formatting (XP/2003)

    Hi,

    Is there a way to use more than 3 conditions with conditional formatting? regards HA

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: more then 3 conditions with conditional formatting (XP/2003)

    With conditional formatting 3 is the limit. VBA could be used to to do this, for an example see Experts Exchange

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

    Re: more then 3 conditions with conditional formatting (XP/2003)

    No, the limit with conditional formatting is three conditions. However, there are a number of ways around this depending on exactly what you are trying to do. For example, you can use VBA code in the Worksheet Change event routine and handle as many conditions as you want. Can you be more specific about what you are trying to do?
    Legare Coleman

  4. #4
    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: more then 3 conditions with conditional formatting (XP/2003)

    Check out <post#=338306>post 338306</post#> and the other items in the thread for a VB approach to doing this.

    Please post back if you have specific quesions on it.

    Steve

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: more then 3 conditions with conditional formatting (XP/2003)

    You could consider using the "normal" number formatting and e.g. use this custom format:

    <!t>[red]<!/t><!t>[<0]<!/t>General,<!t>[blue]<!/t><!t>[>10]<!/t>General,<!t>[yellow]<!/t>General

    Which makes the font red for numbers less than 0, blue for greater than 10 and yellow for all others.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    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: more then 3 conditions with conditional formatting (XP/2003)

    You are also limited in number of conditions with this and it only affects formatting and text color. WIth the right custom format and cond formattiing you could add a few more conditions, but it would get sloppy.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> In US versions, use the ";" not a "," as a separator

    Steve

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: more then 3 conditions with conditional formatting (XP/2003)

    <hr>In US versions, use the ";" not a "," as a separator<hr>
    Ah, I just assumed it needed a comma, since I have a semicolon as list separator. Funny the custom format obviously separates it's arguments with another separator than lists!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: more then 3 conditions with conditional formatting (XP/2003)

    Steve,

    I looked at your referenced post 338306 and didn't seem to follow it all (it's late here also). It also seemed like it was for a very specialised case.

    A long time ago, I asked on this Lounge about the possibility of formatting a cell, depending on its value, like the way some other cell is formatted. I think Legare provided a snippet like:

    You can use something like this in the Worksheet_Change event:

    With Worksheets("Sheet1")
    .Range("A1:A5").Copy
    .Range("D15").PasteSpecial Operation:=xlPasteFormats
    End With


    So it would seem like one could set up some pre-formatted cells, maybe on a separate sheet. Then for the cell in question, check in the Change event if the cell is to have conditional formatting applied ala the above using the Interesect function. If so, then some rules would decide which format would apply. For example, maybe the rule would be based on a value of the cell, look up (using VBA vlookup equivalent for example) for applicable format in the separate sheet and copy the formatting of the cell. Then paste special per above for the cell to be formatted.

    Seems like that should work. Any thoughts?

    Fred

  9. #9
    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: more then 3 conditions with conditional formatting (XP/2003)

    That is the concept used in the other post. It was done differently in that case due to large number of cells to format. It would have been wxtremely slow to paste spaecial all the individual cells.

    It would be possible to have a worksheet change event created to look at the new value in the changed cell(s) and then lookup the format. The problem would be if you want to also change any cells with formulas that change based on the values that are changed. Change event only affects the cells that are edited. If they change by due to a dependency (as in a formula) that is not triggered by this event, since the contents of the cell do not change (the formula is the same), just the value.

    If you want to do something like cond formatting for cells that are edited as well as cells whose values change due to calcs, you will have to base it the calc event and I would recommend not using pasting formats as being too slow. The approach I used in the reference post would be more appropriate. The code is specific to that case, but the concept is the same and the code can be adapted.

    Steve

Posting Permissions

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