ColorIndex with VBA in column until text is found

[Ironman]

Hello - I'm working with the code below. I'm trying to color column A blue, from a2 to a40. However I don't want to go all the way down to row 40, I want to stop where text Grand Total~ is found in Column B, in my case now thats B30...

I think I"m close, but need something in my loop statement that will stop at the point.

Please Login or Register  to view this content.

Thanks for taking a look...

[6StringJazzer]

I wouldn't use a loop. Here is one suggestion.

Please Login or Register  to view this content.

[Ironman]

Thanks 6stringjazzer...This is really really close. My problem now (and I might have explained this well) is that the text 'Grand Total~' will reside in column B, not A. Therefore in running your suggested code I get the message 'text not found'. When i change the 'set' range("a2:B40"), I get get both columns A and B in blue shading. Is there a way that would find 'grand total' in B and only color the cells in A up to A2? So if 'Grand Total~' is in B30, that A2 to A30 are shaded blue. If next week, when I run this report again, and the grand total is in B35, that A2 to A35 are in blue...?

Thanks again for your thoughts, I like the way you did this...

[6StringJazzer]

Oh, I'm sorry. You did explain that, I read it too fast. Try this update.

Please Login or Register  to view this content.

[Ironman]

Right On...i was playing around with different variations of the offset but couldn't quite get it right...thanks a ton! Have a good weekend.