Results 1 to 15 of 15
  1. #1
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [attachment=88647:Image1.jpg]

    I need to colour cells based on multiple criteria:

    Each Rep has his own colour
    (a) Cells containing 1 are coloured to identify Rep
    (b) Cells containing Yes are coloured to identify Rep

    I won't bore you with my unsuccessful attempts to write a change event case statement to do this. Possibly I am totally on the wrong track?

    Could somebody rescue me please?
    Attached Images Attached Images

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    What range is your data in?

    You could just use CF, though it would be dull to set up initially!

    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The real spreadsheet is completed on a rolling month basis. B6:M6 with around 100 rows per month.

    I've tried using XLs built-in CF but cannot seem to get formulas right.

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    What version of Excel?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts
    2002 SP3

  6. #6
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Hi Duffy
    I had to ask about the version because CF changes a lot in 2007. You are no longer limited to three conditions (in 2007).
    Now I notice that your example has 4 sales people. Are you looking for a code solution because you have more than 3 salespeople or because you couldn't get the CF formulas to function?
    In case the second is the situation I'm attaching a sample with CF formulas (of course you'll notice I'm only using 3 salespeople).
    [attachment=88653:Book1.xls]
    If it is a code solution you desire are the salespeople changing all the time? Or would selecting the month and applying the formatting based on the salesperson work for you? I'm asking because using the worksheet change event does increase the overhead on the workbook. If you don't need instantaneous formatting then I'd set up to select and loop through the cells applying formatting.
    Attached Files Attached Files
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  7. #7
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Catherine

    Unfortunately I am stuck with 2002 for the time being...

    Currently I need to format for the same four salespeople* on a rolling month basis. This is a monthly report, so instantaneous formatting is not required.
    The worksheet just needs to update automatically on opening. I think that formatting on basis of month/salesperson would work fine.

    *I'm told this may increase to six at some time in the future (perhaps we'll have 2007 by then)

    Many thanks for the CF formulas. I'll keep them safe for another day.

  8. #8
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Catharine

    Well, I've just spent another couple of days working on this, and am still stuck. I keep getting the good old 400 error. I have now fiddled with my code until I'm sick of the sight of it. I'm hoping to avoid admitting I can't do this. So... as another idea. Suppose I purchased a copy of XL 2007 and wrote the nececessary CF at home - would the CF still work if I then 'imported' it into XL 2002?

    Many thanks
    Duffy

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    No it won't unfortunately. You will need code.
    A sample workbook would be useful, or at least an indication of where exactly the actual data is on the sheet.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Rory

    I guessed that would be the case...

    The column/row heads on the 'real thing' are:

    [attachment=88674:Image2.jpg]

    All and any help much appreciated.

    Duffy
    Attached Images Attached Images

  11. #11
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello Duffy - Can you eliminate any confidential info from your work and then attach a sample file of your work?

    I think that will get you a quick solution.

    Tim

  12. #12
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello Tim

    Unfortunately, the 'real' thing is the New Business Register so just about everything in it is confidential.

    A stripped out version would look pretty much like my mock-up only with a dozen more columns and a hundred or so extra rows.

    I can put something together if it would help.

    Duffy

  13. #13
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello Tim

    Mock up attached. Hope it contains enough information for you to work with.


    All the best
    Duffy
    Attached Files Attached Files

  14. #14
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello Duffy - On your attached mock up, I assumed that the colors for "Mike" in cell I9 and cell L17 should be Green rather than blue.

    Please see if the attached revision is what you were looking for. Please note the Conditional Formatting formulas.

    I first sorted your list by Rep name and then colored the changeable cells for each Rep. For example for Mike I made all of his changeable cells Green.
    Then enter the first Conditional Formula for Mike and use the Format Painter to paint all of his cells with the CF. Be sure to choose "No Color" as the format. Repeat that procedure for each Rep.

    Please note I also added a tab to your workbook for Reps Colors.

    Tim

    [attachment=88681uffy sample.xls]
    Attached Files Attached Files

  15. #15
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Thanks guys for stepping up to answer the question. I keep getting stuck behind a firewall with no access to the lounge. So I start participating in threads and then disappear.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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