Results 1 to 6 of 6

Thread: Conditional formatting based off a few factors

  1. #1

    Conditional formatting based off a few factors



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hey all,

    I've got one which has really started to beat me. I'm trying to set up a conditional formatting, but the requirement is based off a few factors. The run-down:

    - The spreadsheet is to monitor the amount of times a piece of equipment has broken, and subsequently been "changed out" (to fix it).
    - If an "Equipment number" (column A) and "Date of RBB break" (column E) are entered, it is considered a break (i.e. there can be situations where equipment # is entered, but no break date, meaning the entry is NOT a break)
    - Column I ("Date of RBB changeout") needs to turn RED IF >=2 breaks have occurred WITHOUT a changeout

    I'm doing my head in on this one and really want it finished because it's for work! Can someone smarter than me please help!
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	ba4fbeb1.jpg 
Views:	29 
Size:	52.4 KB 
ID:	774  

  2. #2
    Select I4
    Create a New Rule using this formula. =$G4>=2 do not use an IF.
    don't forget to set the fill color to red i the format.

    copy down column I as far as you need it.
    I used column G based on the Red you had highlited in your attached image file.
    Last edited by tommyt61; 2012-09-01 at 03:34 PM.

  3. #3
    Your explanation was not entirely clear (to me at least) what condition you were looking for. your graphic implied one thing but your explanation suggested another. Thought i would share the formula you would use if you need to test column G and H .

    =AND($G4>=2,$H4=0)

  4. #4
    Hey Tommy,

    Thanks for that but not what I'm after sorry.

    The conditions need to be heavily based off the dates entered in "Date of RBB break" and "Date of RBB changeout". So much so I think the answer might need macros.

    In the picture I have just manually entered the colour to show what SHOULD be occuring.
    - The picture gives the example that there are BREAKS on 1/1/12 and 3/1/12. Now This means that a CHANGEOUT is required (because there have been >=2 breaks). In the picture there is a changeout on the 3/1/12, so there is no colour change required.
    - Below this, there was a break on the 10/1/12 and on the 11/1/12, with no subsequent changeout; so those entries need to show up as red (as shown in the picture) UNTIL a changeout date is entered.

    In other words, if there has been >=2 breaks SINCE the last changeout date then those new breaks should show as red (until a changeout date is entered).

    Does that clear it up a bit? Sorry about the confusion but thatnks a lot for the offer of help!

  5. #5
    I'm still confused ...
    Last edited by tommyt61; 2012-09-02 at 03:04 AM.

  6. #6
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    121
    Articles
    0
    Excel Version
    Microsoft Excel 2013
    I think you'll need to base your conditional formatting on the counts of "breaks" and "breaks since changeout" in columns G and H.

    Please see the attached.
    Attached Files Attached Files

Posting Permissions

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