Conditional formatting with more than 15 rules

I don't know what you mean by "violation of the rules" - perhaps you can explain further.

Pete

Are you asking for the warning column to highlight red when the any of the conditions set for that row are met?

@Pete_UK

Sorry for not being explicit enough. With a violation of the rule I meant the fulfilled conditions. The dates (actions taken on a certain date) have deadlines, for example the response has to follow 3 days after the 1st contact. If the condition is not fulfilled, the cell turns red.

@PFDave,

Right, that is what I basically meant.

Originally Posted by lnjkbsn

@PFDave,

Right, that is what I basically meant.

You can use the formatting you have already in place to do this.

Change the applies to field in manage rules and add in the first column you'd like to highlight. You would need to repeat this for each of the rules but once done it's done.

Originally Posted by PFDave

You can use the formatting you have already in place to do this.

Change the applies to field in manage rules and add in the first column you'd like to highlight. You would need to repeat this for each of the rules but once done it's done.

That is also an option but I was hoping that I might avoid setting 15 different rules for 1 cell, instead make only 1 formula which would include all rules.

You only need one CF rule, and you can apply in one go to the cells in column B. First of all, select cells B7:B11 and use the Fill colour icon to change the background to No Fill. With the cells still selected, click on Conditional Formatting | New Rule | Use a Formula... , then enter this formula in the dialogue box:

=OR(G7<-0.03,H7>C7+7,I7>H7+4,J7>H7+7,K7>=J7+14,L7>K7+2)

then click on the Format button | Fill tab and choose red as the background colour, then click OK twice to exit the dialogue box - Excel will adjust the row references to suit the cells that are selected. If you have any other conditions for other columns in your real file, you can just add them to the OR function.

Hope this helps.

Pete

Originally Posted by Pete_UK

You only need one CF rule, and you can apply in one go to the cells in column B. First of all, select cells B7:B11 and use the Fill colour icon to change the background to No Fill. With the cells still selected, click on Conditional Formatting | New Rule | Use a Formula... , then enter this formula in the dialogue box:

=OR(G7<-0.03,H7>C7+7,I7>H7+4,J7>H7+7,K7>=J7+14,L7>K7+2)

then click on the Format button | Fill tab and choose red as the background colour, then click OK twice to exit the dialogue box - Excel will adjust the row references to suit the cells that are selected. If you have any other conditions for other columns in your real file, you can just add them to the OR function.

Hope this helps.

Pete

Thank you very much Pete!

Originally Posted by Richard Buttrey

test reply...........
....One way would be to use a shadow set of 20-30 columns and in each of these columns add an IF test that uses the CF formula which returns a value of 1 if true.

Edit, Sucuri playing up and preventing the rest of this solution that contains example formulae. So in the warning column add a CF formula that sums the range of the shadow column range and sets the CF if the sum is greater than zero.

I am not sure if I understand how to do it. An exampe would be very helpful.