Conditioning and Formatting in VBA

[xXMCP1317Xx]

(blank),A,B,C,D,E,F,G,H,I,J;
Date,2,5,7,8,3,6,12,44,8,16;

(blank),A,B,C,D,E,G,H,I,J;
Opt 1,#,#,#,#,#,#,#,#,#;
Opt 2,#,#,#,#,#,#,#,#,#;
Opt 3,#,#,#,#,#,#,#,#,#;
Total,1,3,4,8,3,4,1,2,12;

Okay so for example commas signify columns and each line is a row.
Also, The letters are meant to signify people.

So lets say the line with date shows how many emails a person received. In the second group the total shows how many they replied to. Sometime there is someone who received an email but has no replies. Signified by the missing F in group two.

If on a new row I want to take the total divided by date. How do I make it so it recognizes that it has to match reference the name for both groups.



Also, once thats solved. the Opt 1-3 is also variable. so the total can shift up and down. how do I condition it so that the row containing total is highlighted and also only have it highlight until cell is blank. So in this example it would only highlight up until it hits the column containing 12.

Hope this is not confusing. The help is much appreciated.

 

[xXMCP1317Xx]

Also, how would you make a dynamic range using a sum if you wanted to sum the row or total and divide by the sum of row of date. But not include the last two cells with values.

In this example it would be from 1-1 not including 2 or 12 on the total. But this needs to be dynamic.