+ Reply to Thread
Results 1 to 14 of 14

Conditional formatting with empty cell

  1. #1
    Registered User
    Join Date
    06-11-2009
    Location
    Swe
    MS-Off Ver
    Excel 2003
    Posts
    27

    Conditional formatting with empty cell

    Hi,
    I want to use conditional formatting on a column ('A') to highlight cells that do not have a match with any cells in a specific range.
    So I've made a range called 'AcceptedData', which contains a couple of cells with text.
    Also included a blank cell in the range, since I do not want blank cells to be highlighted.

    Then I used this conditional format forumla on column A.
    =COUNTIF(AcceptedData;A1)=0

    But it doesn't seem to work. The blank cells get highlighted.
    How do I get around this?
    Last edited by NBVC; 07-09-2009 at 11:14 AM.

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Conditional formatting with empty cell

    In Excel blank cells have a numeric value of zero, so your formula will not work for CF.

    If you want Cf to highlight non-blank cells in the range, you could something like LEN(A1)>0.

    You suggest at the start of your thread that you are matching cells. Possibly you could create a CF formula to look at whether or not a matching value is found.

    Upload your workbook or provide more details if the above is not what you need.

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

    Re: Conditional formatting with empty cell

    Shouldn't formula be?

    =COUNTIF(AcceptedData;A1)=1
    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
    06-11-2009
    Location
    Swe
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: Conditional formatting with empty cell

    Quote Originally Posted by NBVC View Post
    Shouldn't formula be?

    =COUNTIF(AcceptedData;A1)=1
    No, that highlights the things that do match, i want to highlight those that don't.
    Possibly you could create a CF formula to look at whether or not a matching value is found.
    I thought that was what I'd done.
    If you want Cf to highlight non-blank cells in the range, you could something like LEN(A1)>0.
    Thanks.

    How do I combine that with my previous formula? .
    I tried =(COUNTIF(AcceptedData;A1)=0 ) & (LEN(A1)>0)
    But that resulted in no highlightings at all.
    Last edited by dadel; 07-08-2009 at 09:24 AM.

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

    Re: Conditional formatting with empty cell

    =AND(COUNTIF(AcceptedData;A1)=0,LEN(A1)>0)

  6. #6
    Registered User
    Join Date
    06-11-2009
    Location
    Swe
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: Conditional formatting with empty cell

    Oh, so there has to be an 'AND' in the beginning. How wierd, couldn't have guessed that.

    Can't get it to work though. Excel complains about the comma.
    Last edited by dadel; 07-08-2009 at 09:39 AM.

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

    Re: Conditional formatting with empty cell

    replace with semi-colon for your regional settings


    =AND(COUNTIF(AcceptedData;A1)=0;LEN(A1)>0)

  8. #8
    Registered User
    Join Date
    06-11-2009
    Location
    Swe
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: Conditional formatting with empty cell

    Great. Thanks alot!

    Btw, -got a totally unrelated question, but it's so simple it doesn't deserve it's own thread.
    -How do I deselect a cell from a selection?
    (I want to select an entire column, except for the first cell, since that's a headline.)

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

    Re: Conditional formatting with empty cell

    Quote Originally Posted by dadel View Post
    Great. Thanks alot!

    Btw, -got a totally unrelated question, but it's so simple it doesn't deserve it's own thread.
    -How do I deselect a cell from a selection?
    (I want to select an entire column, except for the first cell, since that's a headline.)
    It probably does deserve it's own thread...as it may be a question asked by someone in the future...and they would be able to search it...

    but try selecting the column then hold the CTRL key and click the cell(s) you do not want to include in the selection.

  10. #10
    Registered User
    Join Date
    06-11-2009
    Location
    Swe
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: Conditional formatting with empty cell

    yeah, that's the first thing i tried. doesn't work.

    As a matter of fact, from what i've read after a quick googling it looks like excel doesn't have, and never has had a possibility to deselect from selections. =O
    Wierd.

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

    Re: Conditional formatting with empty cell

    You're right... bad guess.. that's why you should post separately.. someone may give you a better/correct answer

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

    Re: Conditional formatting with empty cell

    One other way might be to Go to EDIT|GO TO and enter the range of interest, click Ok... then copy/paste

  13. #13
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Conditional formatting with empty cell

    (I want to select an entire column, except for the first cell, since that's a headline.)
    If there are no blanks in the column (i.e. data is contiguous down the rows), then select the first cell below the header and press this key combination:
    Ctrl+Shift+down arrow.

    All of the cells that are not empty will be selected. However, if there are blanks between cells then the selection will stop at the first blank cell. At this point you can continue down the column by continuing the press the down arrow.

  14. #14
    Registered User
    Join Date
    06-11-2009
    Location
    Swe
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: Conditional formatting with empty cell

    Thanks. Good tip. Will use that in the future when I don't have as many blank cells as I do now.

    I found a thread from '05 though, with some macros that lets you deselect using ctrl.
    (This should be a sticky or something!)
    http://www.excelforum.com/excel-new-...ted-cells.html

+ 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