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

Lukael

New member
Joined
Feb 9, 2014
Messages
21
Reaction score
0
Points
0
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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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

New member
Joined
Feb 9, 2014
Messages
21
Reaction score
0
Points
0
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 :eek: !

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
 

Attachments

  • Sample-Hours.xls
    20.5 KB · Views: 15

Lukael

New member
Joined
Feb 9, 2014
Messages
21
Reaction score
0
Points
0
Napoleon would say : Veni-Vidi-Vici ! :love:


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

Admin Alumnus
Joined
Oct 13, 2011
Messages
1,779
Reaction score
0
Points
36
Location
Brussels Belgium
Excel Version(s)
2010 on Xubuntu - O365
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

New member
Joined
Feb 9, 2014
Messages
21
Reaction score
0
Points
0
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 :)
 
Top