+ Reply to Thread
Results 1 to 7 of 7

conditional formatting? i want to highlight the cell below based on contents above

  1. #1
    Registered User
    Join Date
    05-05-2008
    Posts
    5

    conditional formatting? i want to highlight the cell below based on contents above

    I have a list like this:

    D6915
    D6915-07
    D6915 - 05b
    D6915 - 05a
    D6915 - 05
    D6914 - 07
    D6915 - 03

    except that the list contains 50,000 rows. I want to highlight every cell below the one that ends in -07, so in this case, I would highlight the one that ends in 05b and 03.

    Someone please please help me with this. I am giving myself carpal tunnel doing this by hand

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523
    Hi there,

    The following macro should do the trick - just make any adjustments as noted in the code:

    Please Login or Register  to view this content.
    HTH

    Robert

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Quote Originally Posted by FrickinGypsy
    I have a list like this:

    D6915
    D6915-07
    D6915 - 05b
    D6915 - 05a
    D6915 - 05
    D6914 - 07
    D6915 - 03

    except that the list contains 50,000 rows. I want to highlight every cell below the one that ends in -07, so in this case, I would highlight the one that ends in 05b and 03.

    Someone please please help me with this. I am giving myself carpal tunnel doing this by hand
    You should be also able to do this via Excel's Conditional Formatting tool...

    Assuming your list starts in row 2, then select vertical range starting at row 2, then invoke the Conditional Formatting from the Format menu.

    Select Formula Is from 1st drop down and enter formula: =RIGHT(A1,2)="07"

    where A1 is the cell directly above the upper most cell in your selection.

    Click Format and select your color from the Pattern tab...
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  4. #4
    Registered User
    Join Date
    05-05-2008
    Posts
    5

    Talking umm im stuck again

    Thank you very much for your quick response!

    Is there a way to modify the formulas you guys gave me to highlight cells with -06, -05, OR -04 and only if there is no highlighting in there already?

    And for some reason, when I asked the original question, I thought it was easy to sort cells by highlighting color. How can I do that?



    Thank you thank you

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    I think you're going to need some VBA for those requests....

    ... hopefully Trebor76 will come back to assist or someone else with the VBA expertise that I don't currently have....

  6. #6
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523
    Hi there,

    The amended code shades the cells as requested - just change the colour to suit.

    There's no inherent function to sort by colour in Excel, but fortunately this User Defined Function (UDF) does the job.

    HTH

    Robert

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    05-05-2008
    Posts
    5

    Talking Thank you guys so much!

    You saved me a lot of time and effort and I appreciate it so much!


+ 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