Conditional formatting based off a few factors

[seno_b]

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!

[tommyt61]

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.

[tommyt61]

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)

[seno_b]

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!

[tommyt61]

I'm still confused ...

[CheshireCat]

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.