Conditional Formatting of Cells based on value of 1 cell - can it be done?

[Legacy 319150]

Hi, this is my first post!. I have a problem that has baffles me and now its time to ask for help - hopefully someone here will be able to tell me if what i want/need is possible or not. It may be really simple or impossibly hard - but i have exhaused all of what i am able to do to get this to work.

What I need is a way of using conditional formatting or VBA (I have very limited knowledge of code) to highlight the cells that cumulatively equal the number of the current stock level, turning the cells green until they match the total then all cells beyond this (colums to the right) turn red. The Purpose of this is to highlight to a customer when the current stock held for their account will run out if not replenished. The columns D:M are sites in the schedule and the rows are the product lines. A Report needs to be issued daily and at present this is a manual task. I have approximately 100 sites at any one time and nearly 200 product lines so takes a vast amount of time to complete the report. The customer wants this visual format so they can easily see the state of the current stock position. The Table below is a sample of what i am working with. I am trying to achieve an automated way so if I update the stock column a Red/Green colour is applied if I have the stock or not. - Is this Possible?

What i have...

Site 1</SPAN>

Site 2</SPAN>

Site 3</SPAN>

Site 4</SPAN>

Site 5</SPAN>

Site 6</SPAN>

Site 7</SPAN>

Site 8</SPAN>

Site 9</SPAN>

Site 10</SPAN>

Part Number</SPAN>

Description</SPAN>

Current Stock</SPAN>

Rqd Qty</SPAN>

Rqd Qty</SPAN>

Rqd Qty</SPAN>

Rqd Qty</SPAN>

Rqd Qty</SPAN>

Rqd Qty</SPAN>

Rqd Qty</SPAN>

Rqd Qty</SPAN>

Rqd Qty</SPAN>

Rqd Qty</SPAN>

1806501024</SPAN>

Part One</SPAN>

15</SPAN>

5</SPAN>

10</SPAN>

12</SPAN>

52</SPAN>

34</SPAN>

41</SPAN>

7</SPAN>

11</SPAN>

27</SPAN>

52</SPAN>

1806501025</SPAN>

Part Two</SPAN>

84</SPAN>

11</SPAN>

33</SPAN>

30</SPAN>

40</SPAN>

29</SPAN>

19</SPAN>

51</SPAN>

36</SPAN>

24</SPAN>

16</SPAN>

1806501026</SPAN>

Part Three</SPAN>

34</SPAN>

8</SPAN>

13</SPAN>

26</SPAN>

10</SPAN>

45</SPAN>

14</SPAN>

53</SPAN>

11</SPAN>

53</SPAN>

50</SPAN>

1806501027</SPAN>

Part Four</SPAN>

178</SPAN>

33</SPAN>

16</SPAN>

54</SPAN>

38</SPAN>

35</SPAN>

16</SPAN>

34</SPAN>

21</SPAN>

29</SPAN>

23</SPAN>

1806501028</SPAN>

Part Five</SPAN>

48</SPAN>

4</SPAN>

12</SPAN>

15</SPAN>

37</SPAN>

44</SPAN>

7</SPAN>

26</SPAN>

7</SPAN>

11</SPAN>

43</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL span=7><COL></COLGROUP>

i can't upload a sample as you can't on this forum and i can't link to a picture/screen shot as i have no way of uploading a picture anywhere. i can however email a copy of a sample sheet if this is easier for anyone - please request from 4oakleigh@gmail.com




Any help would be a life saver!! Thank you in advance

Regards, Darren

 

[Stridhan]

Im not sure if im on the right track here but if you try two formulas for formatting, do you get what you want then?

Select all the requested qty's, starting of in the top left.
Goto conditional formatting, format by formula
( I got swedish version so im not sure if the words are exact )

First formula for green ( D4 is the top left cell in my example and C4 is stock qty)

=SUM($D4:D4)<=$C4

Then add another formula for red

=SUM($D4:D4)>$C4

 

[Snakehips]

Darren,

Welcome to MrExcel.

Assuming I understand correctly then maybe...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1				Site 1	Site 2	Site 3	Site 4	Site 5	Site 6	Site 7	Site 8
2	Part Number	Description	Current Stock	Rqd Qty	Rqd Qty	Rqd Qty	Rqd Qty	Rqd Qty	Rqd Qty	Rqd Qty	Rqd Qty
3	1806501024	Part One	15	5	10	12	52	34	41	7	11
4	1806501025	Part Two	84	11	33	30	40	29	19	51	36
5	1806501026	Part Three	34	8	13	26	10	45	14	53	11
6	1806501027	Part Four	178	33	16	54	38	35	16	34	21
7	1806501028	Part Five	48	4	12	15	37	44	7	26	7
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3	1. / Formula is =AND(D3<>"",SUM($D3:D3)>$C3)	Abc
D3	2. / Formula is =AND(D3<>"",SUM($D3:D3)<=$C3)	Abc

Hope that helps.

 

[Legacy 319150]

Sorry for the delay in responding - thank you to 'Stridhan' for your comments - however from my explaination 'Snakehips' you rock - this is EXACTLY what i was trying to explain - i can't thank you enough.... i know there is always a way with Excel... its just find 'it' or 'someone' who is an excel Guru - you have saved me masses of time daily - THANK YOU!
Kind Regards, Darren

 

[Stridhan]

Thanks for feedback.

(darn, should have added a picture...)

 

[Snakehips]

Darren.

You are welcome. Glad we could help.

The only subtle difference between my formulas and Stridhan's is that mine will not highlight empty cells albeit that they might be subject to the Cfg.

Stridhan, I echo your signature notes.