SUM number values according to days of week and highlight red If exceeds ?

[Lukael]

Hello,

I have these two columns :

(F2 to L2) : 1. (Mon) 2. (Tue) 3. (Wed) 5. (Thu) 6. (Fri) 7. (Sat) 8. (Sun)

(F4 to L4) : .11-13 .6-14 .6-13 .7-15 .7-15 .7-15 .7-15


Question : IF SUM of number values exceeds =56 from Monday to Sunday, I want to highlight all number cells ( those which are SUM) with RED, or optional a Pop-up window like "You exceeded value ! ". Is It possible ?

thanks for your help !

Regards, Luka

[Ken Puls]

Sure thing. You need a conditional formatting rule.

Not sure of your version of Excel, but if you're 2007 or higher then:

• Select cells F4:L4 (start from F4)
• Home Tab --> Conditional Formatting --> New Rule --> Use a formula to determine which cells to format --> Use the following formula
• =SUM($F$4:$L$4)>56
• Click "Format" --> Font --> Color --> Red
• Click OK --> OK

At this point it should turn red every time the aggregate in the cells exceeds 56

Hope this helps,

[Lukael]

Yes, I did that before posting this thread, but the problem is that when I select all cells and do as you instructed, It doesn't highlight all cells, but only 6 of them (If formula is TRUE or FALSE), and 1 of them only If formula meets desired value. I have to do this in my monthly worksheet for each week, and for around 70 rows, which is quite a long-time mouse-clicking If done one cell-by-one cell !

I attached sample worksheet and you can see it yourself !

P.S.: formula is in B3 cell, conditional formatting should apply from B2-H2 !


Maybe I don't know how to select properly all cells, but you will see in Edit Rule that everything is as It should be !


Thanks for respond,

Regards, Luka

[Pecoflyer]

Does the attached help ?
The formula for the CF is

=SUMPRODUCT(($A$1:$AE$1-WEEKDAY($A$1:$AE$1,3)=A$1-WEEKDAY(A$1,3))*$A$2:$AE$2)>=56

Sample-Hours-1.xls

[Lukael]

Napoleon would say : Veni-Vidi-Vici !


It works great, just tell me this :

1. How did you manage to work It for all cells, did you select cells in some special way ?

2. What was wrong with my formula ?

3. I noticed that you didn't insert your formula in any cell - just in rule of CF, is that right ?

[Pecoflyer]

1. I don't understand the question
2. The best way to examine what happens in CF is to copy your formula in an empty cell in col B and drag right. There where it gives TRUE and should return FALSE , use the Evaluate formula tool to see what goes wrong
3. Errrr, AFAIK that's the only way to implement CF ?

[Lukael]

OK, don't worry about my questions, I'm Excell beginner. Could you explain your code in one or two words (how It works), I don't understand It quite good.

And thank you, I have tried It in my workbook and It just works perfectly