+ Reply to Thread
Results 1 to 6 of 6

Conditional formatting. Highlight cells that meet a criteria but ignore blank cells.

  1. #1
    Registered User
    Join Date
    09-02-2010
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2003
    Posts
    18

    Conditional formatting. Highlight cells that meet a criteria but ignore blank cells.

    -I have two columns of numbers.
    -There are blank cells in each column that appear sporadically.
    -In each row of numbers, I want to create a conditional format that highlights a cell if the number in the cell is greater than a designated value.
    -Currently I am using: Conditional formatting 'formula' =ABS(C4-C3)>.1 OR =ABS(D4-D3)<.1 for the respective column of numbers.
    -This formula does not work now that I have empty cells between the values. All the blank cells are highlighted and the subtraction can not take place if the cell is blank.
    -I want the formula to not see the blank cells and make the comparison between the first vale and the next vale in the series.
    -I do not want to sort the blank rows out and then replace.
    Thank you,
    Daniel

    Please see attachment: TESTAE042701.csv
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Conditional formatting. Highlight cells that meet a criteria but ignore blank cells.

    You dont say if you are testing the cell above or below, but 1 of these should work for you...

    =and(C3<>"",ABS(C3-C2)>.1) OR =and(D3<>"",ABS(D3-D2)<.1)
    or
    =and(C2<>"",ABS(C3-C2)>.1) OR =and(D2<>"",ABS(D3-D2)<.1)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    09-02-2010
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2003
    Posts
    18

    Re: Conditional formatting. Highlight cells that meet a criteria but ignore blank cells.

    Please see revised attachment...
    Column C row 22.

    This is the instance where the formula will not highlight the value. Above or below doesn't matter. These numbers are plotted linearly and any values that are not within the specified range will create spikes. I am trying isolate the "bad" values.

    Thank you.
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Conditional formatting. Highlight cells that meet a criteria but ignore blank cells.

    Why are you uploading a .csv file? The cannot contain any formatting you may have applied already. Please upload the sample excel workbook

  5. #5
    Registered User
    Join Date
    09-02-2010
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2003
    Posts
    18

    Re: Conditional formatting. Highlight cells that meet a criteria but ignore blank cells.

    Please see attached .xls. Thank you.
    Attached Files Attached Files

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Conditional formatting. Highlight cells that meet a criteria but ignore blank cells.

    If I look at C22, it contains 0.004
    Are you looking to test (and ignore) blank cells above?

    Try this. In E3, copied down, use this...
    =IF(ISNUMBER(C3),C3,E2)

    Then change the CF in column C to this...
    =ABS(E3-E2)>0.1

+ 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