+ Reply to Thread
Results 1 to 5 of 5

ColorIndex with VBA in column until text is found

  1. #1
    Forum Contributor
    Join Date
    01-16-2009
    Location
    Ill.
    MS-Off Ver
    Excel 2010
    Posts
    190

    ColorIndex with VBA in column until text is found

    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...
    Last edited by Ironman; 03-18-2011 at 03:19 PM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: ColorIndex with VBA in colun til text if found

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

    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Contributor
    Join Date
    01-16-2009
    Location
    Ill.
    MS-Off Ver
    Excel 2010
    Posts
    190

    Re: ColorIndex with VBA in colun til text if found

    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...

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: ColorIndex with VBA in colun til text if found

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

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    01-16-2009
    Location
    Ill.
    MS-Off Ver
    Excel 2010
    Posts
    190

    Re: ColorIndex with VBA in colun til text if found

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1