+ Reply to Thread
Results 1 to 11 of 11

conditionally format the first value to the right of a blank cell

  1. #1
    Registered User
    Join Date
    09-11-2012
    Location
    Rhode Island, USA
    MS-Off Ver
    2007
    Posts
    12

    conditionally format the first value to the right of a blank cell

    I want to highlight the first cell with a value that applies to the left of a blank. Any ideas?

    Thanks,

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: conditionally format the first value to the right of a blank cell

    Select your range, Let's say B2:Z100
    Conditional Formatting> New Rule>Use Formula
    =AND(ISBLANK(B1),NOT(ISBLANK(B2)))
    Does that work for you? It should if they are truly blanks

    EDIT: Opps, got my lefts and rights mixed up
    With same range,
    =AND(ISBLANK(B3),NOT(ISBLANK(B2)))
    Last edited by ChemistB; 09-13-2012 at 04:54 PM.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: conditionally format the first value to the right of a blank cell

    Assuming you want to highlight column F where first occurance of blank cell in G.

    Select column F and Home|Conditional formatting|New Rule

    use a formula to determine which cells to format

    enter: =COUNTIF(G$1:G1,"")

    click Format and choose...
    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
    09-11-2012
    Location
    Rhode Island, USA
    MS-Off Ver
    2007
    Posts
    12

    Re: conditionally format the first value to the right of a blank cell

    Doesn't seem to work.

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: conditionally format the first value to the right of a blank cell

    Here is my interpretation.. only first cell to left of a blank is highlighted.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-11-2012
    Location
    Rhode Island, USA
    MS-Off Ver
    2007
    Posts
    12

    Re: conditionally format the first value to the right of a blank cell

    Quote Originally Posted by NBVC View Post
    Assuming you want to highlight column F where first occurance of blank cell in G.

    Select column F and Home|Conditional formatting|New Rule

    use a formula to determine which cells to format

    enter: =COUNTIF(G$1:G1,"")

    click Format and choose...
    I have a range of cells that is going to be expanding. A blank can occur anywhere in the range. I would like to highlight any cell that contains a value and that has a blank to the left of it. There could be several blanks in a row with non blank items mixed in. I don't want to highlight cells that have a value to the left of it, but have a blank farther to the left. I hope I have explained it clearly.

  7. #7
    Registered User
    Join Date
    09-11-2012
    Location
    Rhode Island, USA
    MS-Off Ver
    2007
    Posts
    12

    Re: conditionally format the first value to the right of a blank cell

    I am trying to highlight the first occurance of a value in the baseline field of this book.
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: conditionally format the first value to the right of a blank cell

    Okay, I used a range of D2:X50 and applied this condition

    =AND(ISBLANK(E2),NOT(ISBLANK(D2)))
    See attachment. Is that what you are looking for?
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    09-11-2012
    Location
    Rhode Island, USA
    MS-Off Ver
    2007
    Posts
    12

    Re: conditionally format the first value to the right of a blank cell

    That is close. It seems to be checking for a blank to the right of a cell instead of to the left of a cell. Everything else seems to be the way I would like it. Thanks.

  10. #10
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: conditionally format the first value to the right of a blank cell

    Okay, change the conditional formatting formula to

    =AND(ISBLANK(C2),NOT(ISBLANK(D2)))
    Does that work for you?

  11. #11
    Registered User
    Join Date
    09-11-2012
    Location
    Rhode Island, USA
    MS-Off Ver
    2007
    Posts
    12

    Re: conditionally format the first value to the right of a blank cell

    That works perfectly... Thank you very much to everyone that was helping.

+ 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