+ Reply to Thread
Results 1 to 3 of 3

Conditional formatting using keywords, 2010

  1. #1
    Registered User
    Join Date
    11-12-2010
    Location
    Colmar, PA, USA
    MS-Off Ver
    Excel 2003
    Posts
    2

    Conditional formatting using keywords, 2010

    Hello all,

    I need some help with specific conditional formatting. I have a workbook that monitors warranty returns on a weekly basis, where a new week is added as a new column each week (ie, one week that is in column Q changes to column R in the following week, which in turn becomes the Q column), and however many of a particular item were returned is entered on that item's row.

    So far I have it set to color each number's box according to the size of the number, with zeroes remaining white. This uses a three-range color gradient for all numbers greater than one in one rule, and white for all zeroes in another rule.

    Now, the tricky part. When something comes back for the first time, it's labeled as "New" in a particular column. I need to have the box for that week's number to be a specific color, independent of the aforementioned 3-color gradient range (so, even if the gradient says the number should be green or yellow, I need it to be red). It must remain that way as the weeks pass until it comes in again, at which point it is no longer "New" and should be governed by the gradient.

    Ideas? Am I being unclear? Let me know!

    Thank you!

    BTW, this is with Excel 2010

  2. #2
    Valued Forum Contributor Blake 7's Avatar
    Join Date
    10-01-2010
    Location
    Bogota, Colombia
    MS-Off Ver
    Excel 2010 64 bit and Excel 2007,
    Posts
    1,377

    Re: Conditional formatting using keywords, 2010

    Hi Knapp, I would seriously recommend uploading your workbook and describe your desired result there.

    Cheers
    Blake 7

    If your question has been answered, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

    If any member's response has helped to solve your problem, please consider using the scales icon on the top right of their post to show your appreciation.

    http://failblog.org/

  3. #3
    Registered User
    Join Date
    11-12-2010
    Location
    Colmar, PA, USA
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Conditional formatting using keywords, 2010

    Certainly. Here is a quick example of what I have, limited to 25 items (actual workbook can be north of 7,000). I used a RANDBETWEEN function to generate random number for this example, then replaced certain values with a dash to represent zero (is it does in the full workbook).

    Notice that lines 4, 11, and 17 have a low value in the first colored column (column C) (and so it appears green), and the rest in those rows are zero. This means that, until now, there have not been any occurances for these three items. Thus, they are labeled "New" in column B. What I need is for those values to be colored red, meaning the gradient that governs everything else is overridden until the there is a new occurance (say, three or more weeks from now give three additional columns, and line 4 suddenly has another occurance... it loses its "New" status and returns to being governed by the gradient).

    Let me know if that's unclear.

    Thanks!
    Attached Files Attached Files

+ 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