Hi ExcelFox
Thanks
I am slowly learning VBA ( alot from this site)
I did try by adding the following code which i changed from bakerman original code(very helpful)...bakerman then came back and fixed it to work for other options

I am not sure how to progress from removing the color in the rows which have the Words Total and Grand Total....i will have another look and see if i can get it...thanks again

This is my code....but bakerman did change it further to work
Code:
 With .Range("B" & Rows.Count).End(xlUp)
    .Offset(, 1).Resize(, 4).Interior.ColorIndex = 34
    .Offset(, 8).Resize(, 9).Interior.ColorIndex = 34
Code:
 With .Range("B" & Rows.Count).End(xlUp)
    .Offset(, 5).Resize(, 12).Interior.ColorIndex = 34
Code:
With .Range("B" & Rows.Count).End(xlUp)
    .Offset(, 1).Resize(, 7).Interior.ColorIndex = 34 'INTERNAL
    .Offset(, 13).Resize(, 4).Interior.ColorIndex = 34 'INTERNAL
    .Offset(, 1).Resize(, 12).Interior.ColorIndex = 34 'EXTERNAL