Results 1 to 4 of 4
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Applying a 6 colour conditional format (Excel 2000>)

    Edited by HansV to prevent <!t>[red] etc. from being used as color tags
    And also to make link work


    I discovered a way to create a 6 colour conditional format. The way to do this is to combine the standard 3 colour formatting of the Conditional Format feature with a custom number format using the Positive, Negative and Zero parts of a custom number.

    To do this:

    - Select the range of numbers in the sheet
    - Choose Format, Cells, and select the Numbers Tab
    - Choose Custom (category) and set up a custom format like this: [Color3][<10]General;[Color5][<20]General;<!t>[Green]General
    * A custom number is formatted in 4 parts: Positive; Negative; Zero; Text. (Here we use the first 3 parts and assign colour and a condition)
    - Choose OK to assign the colours
    - Keeping the range selected, now choose Format, Conditional Formatting
    - Set up the next 3 formats according to your preference. (Remember to be systematical in the condition/number assignments)
    - Choose OK

    You should now have a 6 banded number scheme, based on your criteria.

    PS: This is not necessary in Excel 2007. In this version you can create many conditions using the new dialog interface.
    PPS: See the attached file for an example.
    PPPS: I guess this (amazing) lounge came up with this first thanks to <!profile=Pieterse>Pieterse<!/profile>. See the thread starting at <post#=273,182>post 273,182</post#>
    TX for the edit Hans...I wondered about those colours... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    More examples and methods using Google Search:
    http://exceltips.vitalnews.com/Pages/T0457...al_Formats.html
    http://www.ozgrid.com/Excel/font-formats.htm
    Attached Files Attached Files
    Regards,
    Rudi

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

    Re: Applying a 6 colour conditional format (Excel 2000>)

    This method has been mentioned by Pieterse (<post:=251,940>post 251,940</post:>) and probably others. It works well, but it is more limited than conditional formatting:
    - It can only change the font color, not borders and shading.
    - You can only specify conditions on the cell value, not a formula as in conditional formatting.
    For more complex requirements, VBA code is required.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Applying a 6 colour conditional format (Excel 2000>)

    Yes. I noticed the limitation when I read another post from <!profile=Pieterse>Pieterse<!/profile>. TX.

    Something else that is interesting though about the colour formats is that you can use [Color1], [Color2], .... [Color56]. Many people are under the impression that only the constants can be used, ie. <!t>[Red], <!t>[Blue], <!t>[Magenta], etc...

    PS: Thanks for the tip about the Tag. Strange that you do not need the end tag here?
    Regards,
    Rudi

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

    Re: Applying a 6 colour conditional format (Excel 2000>)

    Officially, you need to use <!t>[t]<!/t>Green<!t>[/t]<!/t> if you want <!t>[Green] in your post instead of turning the text green. But using <!t>[t]<!/t> at the beginning is sufficient - the Lounge software doesn't recognize <!t>[t]<!/t>Green] as a color tag.

    Thanks for the tip about [Color1] etc. I didn't know that either!

Posting Permissions

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