+ Reply to Thread
Results 1 to 2 of 2

Conditional formatting based on keywords in changing list

  1. #1
    Registered User
    Join Date
    04-27-2016
    Location
    Brooklyn
    MS-Off Ver
    2010
    Posts
    46

    Conditional formatting based on keywords in changing list

    I have a conditional formatting rule set to highlight a cell if certain keywords are present in the text of the cell using this formula:

    =SUMPRODUCT(IFERROR(FIND(A4:A100,C5),0))>0

    where A4:A100 is a list of keywords.

    This is working but the problem is the list keywords needs to be able to be edited/added or deleted to and from. So when any of the cells in A4:A100 are empty, it results in the targeted cell also being highlighted if C5 is empty, which is an undesired effect. Is there any way to set the conditional formatting to ignore the blank cells in the A4:A100 range?

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Conditional formatting based on keywords in changing list

    Try this as the CF formula:

    =SUMPRODUCT((IFERROR(FIND(A4:A100,C5),0))*(A4:A100<>""))>0

    Hope this helps.

    Pete

+ 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] Changing Conditional Formatting Rules Based on Vlookup?
    By neato in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-04-2016, 02:21 PM
  2. Replies: 1
    Last Post: 10-05-2011, 06:59 AM
  3. Conditional formatting based on changing dates?
    By Eroos in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-11-2011, 11:14 AM
  4. changing conditional formatting based on a reference
    By altsean06 in forum Excel General
    Replies: 1
    Last Post: 05-12-2011, 06:17 PM
  5. Replies: 3
    Last Post: 01-18-2011, 10:31 AM
  6. Conditional formatting using keywords, 2010
    By rknapp in forum Excel General
    Replies: 2
    Last Post: 11-17-2010, 05:07 PM
  7. Changing font type based on conditional formatting
    By LegendaryPunk in forum Excel General
    Replies: 1
    Last Post: 01-20-2010, 02:48 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