Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    More Than 3 Conditional Formats? (Excel 97 SR2)

    The search function is offline.....

    Has anyone found a non-VBA way of applying more than 3 conidtional formats? Sort of thing I would like to be able to do:

    BackgroundColour=BackgroundColour.INDEX(lstSomeNam e,SomeIndex)

    i.e. The array "lstSomeName" will have it's individual cells colour coded, by hand. Say this is single column of 10 cells with the values 1-10. What i want to do elsewhere is format the background colour of a cell based on it's data, 1-10, by referring back to the original array and discovering what the background colour is there.

    Regards
    Peter

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

    Re: More Than 3 Conditional Formats? (Excel 97 SR2)

    No non-VBA way.

    You can however have the font colour change conditionally using the numberformat string:

    E.g.:

    [ red][>100]General;[ blue][>10]General;[ green]General

    Note you must remove the spaces before the colours (I had to include them to avoid the lounge using them as formatting codes)!!

    Combine this with the conditional format to get up to 6 (I guess <g>) regions.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More Than 3 Conditional Formats? (Excel 97 SR2)

    Hi Jan

    Thank you... and apologies for not replying earlier. Unless I'm being slow, always entirely possible, this won't help me extend this formatting to the right. i.e. I have a row of data to which I want to automatically apply this formatting so the formatting for the row is dependent upon the value in a cell on the left of the sheet. Not a problem as such, just one of those 'nice to do things' which is far from being essential.

    Regards
    Peter

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

    Re: More Than 3 Conditional Formats? (Excel 97 SR2)

    You're not slow. Indeed my suggestion to use the custom number format only works on the data in the cell itself, whereas conditional formatting can be based on formulas using references to any (set of) cell(s).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More Than 3 Conditional Formats? (Excel 97 SR2)

    Maybe I'll give in, one day, and write it as a VBA function. Though it's not important enough to put any 'work time' into it, so it'll be another one of those interesting 'spare time' projects. i.e. The ones I REALLY enjoy! <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

    Regards
    Peter

Posting Permissions

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