+ Reply to Thread
Results 1 to 18 of 18

Conditional Formatting - Colour Scales

  1. #1
    Registered User
    Join Date
    01-22-2010
    Location
    Milton Keynes
    MS-Off Ver
    Excel 2007
    Posts
    15

    Conditional Formatting - Colour Scales

    Hello All,
    Is there a way of setting up a spreadsheet so that the 'colour scales' available in 2007 will apply to each row at a time. I can go through each row one by one and format them manually to show the highest and lowest price in that row. However as soon as I try and auotomate the process or drag the formatting down the page it incorporates the prices on other rows which I want to avoid. It will make more sense if you look at the attachment.
    Thanks
    gareth
    Attached Files Attached Files

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional Formatting - Colour Scales

    You need to remove the $ signs from before the row numbers in the "Applies to" fields of the Conditional Format window.

    The $ makes the row absolute (i.e. it refers always to the row)
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    01-22-2010
    Location
    Milton Keynes
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Conditional Formatting - Colour Scales

    I tried that by going into 'managing rules' the but as soon as I 'apply' it the $ are put back into place?

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional Formatting - Colour Scales

    See attached.. is that what you need?
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-22-2010
    Location
    Milton Keynes
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Conditional Formatting - Colour Scales

    No unfortunately it isn't. Each row needs to be formatted individually, row by row. The point is that by using the colour scale you should be able to see which shop is selling each product at the highest price and the lowest and proportionally each one within those two extremes.
    Gareth

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Conditional Formatting - Colour Scales

    Delete all the rules except #1, then edit the applies to enclude the entire desired range:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Ben Van Johnson

  7. #7
    Registered User
    Join Date
    01-22-2010
    Location
    Milton Keynes
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Conditional Formatting - Colour Scales

    Sorry I am obviously not making myself clear. By doing the changes you suggest you end up getting a colour scales that show the highest and lowest figures incorporating EVERY figure available. NOT line by line. So for example in the first row I need it to show which company shown is selling the Airline 77 QE Channel 1 system at the highest price (Red Dog) and the lowest (GAK ). Then I need it to do the same thing for the next row in isolation, and the next etc etc.
    Thanks for your help it is appreciated.
    Gareth
    Attached Files Attached Files

  8. #8
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Conditional Formatting - Colour Scales

    I had to resort to code in order to apply a row by row condition.

    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

  9. #9
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Conditional Formatting - Colour Scales

    Select your B2:X2 and apply the dual color scale formatting. Fine tune to your liking.
    Select the same cells again and copy.
    Select B3:X219 and Paste - Special - Formats.

    Edit: Scratch that. Does not work as expected.
    Last edited by teylyn; 03-10-2010 at 05:24 AM.

  10. #10
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: Conditional Formatting - Colour Scales

    I don't think that works Teylyn,

    The new rule applies to B3:X219 as where there should be 219 rules, for each row a different rule like in Andy's code

    Please Login or Register  to view this content.
    Last edited by rwgrietveld; 03-10-2010 at 05:46 AM.
    Looking for great solutions but hate waiting?
    Seach this Forum through Google

    www.Google.com
    (e.g. +multiple +IF site:excelforum.com/excel-general/ )

    www.Google.com
    (e.g. +fill +combobox site:excelforum.com/excel-programming/ )

    Ave,
    Ricardo

  11. #11
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Conditional Formatting - Colour Scales

    Quote Originally Posted by rwgrietveld
    I don't think that works Teylyn,
    that's why I scratched it.
    This whole newfangled colorscheme thing is mighty confusing. No wonder people get lost.

  12. #12
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: Conditional Formatting - Colour Scales

    mighty confusing ? Maybe it is just designed wrong.

    It automattically re-sets the $ sign. That is something that (IMHO) should not happen as it doesn't in other CF functionality !

    Even NBVC seems to be confused or seems to find it illogical.

  13. #13
    Registered User
    Join Date
    01-22-2010
    Location
    Milton Keynes
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Conditional Formatting - Colour Scales

    I am glad it is not just me!
    I will try the macro given and let you know how I get on
    Gareth

  14. #14
    Registered User
    Join Date
    01-22-2010
    Location
    Milton Keynes
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Conditional Formatting - Colour Scales

    Hurrah! Andy you are a star. I have got my life back I don't need to colour scale every bloody line ever other day.
    Your not a musician by any chance?

  15. #15
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: Conditional Formatting - Colour Scales

    Your not a musician by any chance?
    Teylyn is ...

  16. #16
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Conditional Formatting - Colour Scales

    ... grumble .... Yeah! Rub it in.

    Playing D flat minor now.

  17. #17
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Conditional Formatting - Colour Scales

    Your not a musician by any chance?
    I wish. I'm tone deaf with bad timing

  18. #18
    Registered User
    Join Date
    10-15-2010
    Location
    southampton, england
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Conditional Formatting - Colour Scales

    hello

    thanks very much for the code that was posted it works great however i am really new with code and i was just wondering how i change the colours. at the moment high numbers are highlighted in red and low in green. i would like to reverse this please.

    thanks in advance.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1