Change Cell Color If Individual Month % Total is Over 100%

[asalazar]

Hello,

I need some help creating VBA code where the cell color changes to yellow if an individual’s total for that particular month is over 100%.

Here is a sample of my data:

[TABLE="width: 981"]
<colgroup><col><col span="2"><col><col span="12"></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Title[/TD]
[TD]Plan[/TD]
[TD]Notes[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD][/TD]
[TD]2%[/TD]
[TD]2%[/TD]
[TD]2%[/TD]
[TD]10%[/TD]
[TD]10%[/TD]
[TD]10%[/TD]
[TD]10%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]40%[/TD]
[TD]40%[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10%[/TD]
[TD]10%[/TD]
[TD]10%[/TD]
[TD]10%[/TD]
[TD]10%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20%[/TD]
[TD]20%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]75%[/TD]
[TD]75%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]80%[/TD]
[TD]80%[/TD]
[TD]80%[/TD]
[TD]80%[/TD]
[TD]80%[/TD]
[TD]80%[/TD]
[TD]80%[/TD]
[TD]80%[/TD]
[TD]80%[/TD]
[TD]25%[/TD]
[TD]25%[/TD]
[TD]80%[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]7%[/TD]
[TD]7%[/TD]
[TD]7%[/TD]
[TD]7%[/TD]
[TD]7%[/TD]
[TD]7%[/TD]
[TD]7%[/TD]
[TD]7%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]7%[/TD]
[/TR]
[TR]
[TD]Nick Rank[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD][/TD]
[TD]2%[/TD]
[TD]2%[/TD]
[TD]2%[/TD]
[TD]10%[/TD]
[TD]10%[/TD]
[TD]10%[/TD]
[TD]10%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]40%[/TD]
[TD]40%[/TD]
[/TR]
[TR]
[TD]Nick Rank[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[/TR]
[TR]
[TD]Nick Rank[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10%[/TD]
[TD]10%[/TD]
[TD]10%[/TD]
[TD]10%[/TD]
[TD]10%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nick Rank[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nick Rank[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20%[/TD]
[TD]20%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nick Rank[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nick Rank[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]75%[/TD]
[TD]75%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nick Rank[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]80%[/TD]
[TD]80%[/TD]
[TD]80%[/TD]
[TD]80%[/TD]
[TD]80%[/TD]
[TD]80%[/TD]
[TD]80%[/TD]
[TD]80%[/TD]
[TD]80%[/TD]
[TD]25%[/TD]
[TD]25%[/TD]
[TD]80%[/TD]
[/TR]
[TR]
[TD]Nick Rank[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]7%[/TD]
[TD]7%[/TD]
[TD]7%[/TD]
[TD]7%[/TD]
[TD]7%[/TD]
[TD]7%[/TD]
[TD]7%[/TD]
[TD]7%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]7%[/TD]
[/TR]
</tbody>[/TABLE]


The Macro should then color in yellow all in the cells in Yellow for John Smith for the following months because the individual's month totals are greater than 100%:
Jun - Feb

For Nick Rank the Macro should color in yellow all the cells for the following months because the individual's month totals are greater than 100%:
Jul-Sep & Dec & Feb

Thank in Advance,

Anthony

 

[asalazar]

Just sending this to correct the following statement:

Incorrect:
The Macro should then color in yellow all in the cells in Yellow for John Smith for the following months because the individual's month totals are greater than 100%:
Jun - Feb

Correct:
The Macro should then color in yellow all in the cells for John Smith for the following months because the individual's month totals are greater than 100%:

 

[Joe4]

I don't think you need VBA for this. I think Conditional Formatting will do it.
Let's say that your data is in cells A1:P19.
Then select cells E2:P19 and enter this Conditional Formatting formula:

=SUMIF($A:$A,$A2,E:E)>100%

and choose your yellow color.

 

[asalazar]

You are correct Joe4, thank you very much!

 

[Joe4]

You are welcome!