+ Reply to Thread
Results 1 to 10 of 10

Conditional formatting needs to ignore blank spaces

  1. #1
    Registered User
    Join Date
    01-07-2014
    Location
    South Africa
    MS-Off Ver
    Excel 2010/13
    Posts
    5

    Conditional formatting needs to ignore blank spaces

    Hello everyone. Thank you for reading and helping if you are.

    I need to accomplish the following I have used a conditional formatting formula =$E1=$A$1. that uses search box in A1 to look for words in column and then highlights the entire row that word is in. In this case it is aircraft registrations.

    if I don't have something written in the search box in A1 the selected area is highlighted that does not have any data in.

    Can someone please help me to have my formula ignore all blank areas when there is nothing written in A1
    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 needs to ignore blank spaces

    Hi and welcome to the forum Hoe gaan dit darr by jou?

    Try this in the CF rule...
    =AND($E2<>"",$E2=$A$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
    01-07-2014
    Location
    South Africa
    MS-Off Ver
    Excel 2010/13
    Posts
    5

    Re: Conditional formatting needs to ignore blank spaces

    Good morning Mr Dibbins. Dit gaan baaie goed met my en met jou :-)

    OK so basically I just add that after the initial rule? Will try that and let you know. Thank you for the help. I see my typing is much better at 09 in the morning than at night.

  4. #4
    Registered User
    Join Date
    01-07-2014
    Location
    South Africa
    MS-Off Ver
    Excel 2010/13
    Posts
    5

    Re: Conditional formatting needs to ignore blank spaces

    OK I added that to the existing line but all it does is highlight line 1 and 2 when there is nothing typed in A1

  5. #5
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional formatting needs to ignore blank spaces

    you need just a small modification to FDibbins formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  6. #6
    Registered User
    Join Date
    01-07-2014
    Location
    South Africa
    MS-Off Ver
    Excel 2010/13
    Posts
    5

    Re: Conditional formatting needs to ignore blank spaces

    Guys what can I say thank you so very very very much

  7. #7
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional formatting needs to ignore blank spaces

    Welcome to the forum.

    You are welcome and thanks for the feedback.

  8. #8
    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 needs to ignore blank spaces

    I forgot to say that you should start your CF rule range from row 2, not row 1

  9. #9
    Registered User
    Join Date
    01-07-2014
    Location
    South Africa
    MS-Off Ver
    Excel 2010/13
    Posts
    5

    Re: Conditional formatting needs to ignore blank spaces

    Baie dankie Dibbins :-)

  10. #10
    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 needs to ignore blank spaces

    Happy to help where in SA? (I lived in PE, Orkney and Klerksdorp)

+ 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. Replies: 6
    Last Post: 09-20-2020, 06:58 PM
  2. Replies: 5
    Last Post: 05-18-2013, 07:49 PM
  3. [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
  4. How to ignore blank cells in conditional formatting?
    By Gooford in forum Excel General
    Replies: 5
    Last Post: 08-22-2012, 08:06 AM
  5. Ignore Spaces in "Blank" Cells
    By Oriana in forum Excel General
    Replies: 3
    Last Post: 09-25-2007, 01:26 PM

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