+ Reply to Thread
Results 1 to 4 of 4

Condition Formatting - Can't get it to ignore blank rows

  1. #1
    Registered User
    Join Date
    09-15-2014
    Location
    QLD Australia
    MS-Off Ver
    2010
    Posts
    18

    Condition Formatting - Can't get it to ignore blank rows

    Hi everyone,

    I am working on a document with some conditional formatting to add some colour coding.

    It's all going ok, except that I need for rows with overdue responses (column k) to be highlighted in red.
    It ends up highlighting all the rows which are also blank.
    When I use format only cells that contain blanks - no formatting, it whites out all the colour except for a few seemingly random boxes covered by other conditions - no matter how I fiddle with the order.

    Can anyone offer any help with this?

    Document attached - highlighted all in red like crazy XD

    Also, would there be a way to highlight the rows using a vba macro instead? Would that be more or less effective/prone to error than conditional formatting?
    Attached Files Attached Files
    Last edited by ania1224; 04-10-2018 at 02:56 AM. Reason: solved

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Condition Formatting - Can't get it to ignore blank rows

    change the formula to include a check for blank cell

    sucuri wont let me post te formula so i have to post the file instead X_X
    Attached Files Attached Files
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    09-15-2014
    Location
    QLD Australia
    MS-Off Ver
    2010
    Posts
    18

    Re: Condition Formatting - Can't get it to ignore blank rows

    Quote Originally Posted by humdingaling View Post
    change the formula to include a check for blank cell

    sucuri wont let me post te formula so i have to post the file instead X_X
    Thanks so much for the help. I can see the formula you have used! I trued something similar, but not quite, but all it did was complain at me that I was typing invalid entries into the formula input. Excel just does not seem to like me sometimes....

    Thankyou again

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Condition Formatting - Can't get it to ignore blank rows

    conditional formatting is like that
    takes a bit of practice to get used to the syntax as its slightly different to rest of Excel
    you have to be rather exact with your absolute references (ie $ placement) in the formula as well as where it applies to

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How To Ignore Blank Cells with Conditional Formatting
    By gloriousglenn in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-16-2015, 12:03 PM
  2. Index Match Function? Ignore blank rows - (rows without values)
    By jgray in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-26-2015, 01:12 PM
  3. Conditional Formatting - ignore Blank Cells
    By therealtfloss in forum Excel General
    Replies: 1
    Last Post: 02-09-2015, 11:38 AM
  4. Condition Formatting: when one cell had data entered, ignore another
    By FarazM in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-24-2014, 06:16 PM
  5. Conditional formatting needs to ignore blank spaces
    By Siciro in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-08-2014, 11:50 AM
  6. [SOLVED] How to get Conditional Formatting to ignore blank cells?
    By AndyHawke in forum Excel General
    Replies: 3
    Last Post: 08-22-2012, 08:25 AM
  7. How to ignore blank cells in conditional formatting?
    By Gooford in forum Excel General
    Replies: 5
    Last Post: 08-22-2012, 08:06 AM

Tags for this Thread

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