Use Conditional Formatting to to Highlight Cells with Specific text and IF formula condition

mminchev

New Member
Joined
Feb 16, 2018
Messages
16
I have 3 cells that I am looking at:

Contact Name
Name Word Count
Name Fix
John John Doe3John Doe
Bear Bear Joe3Bear Joe

What I need is to run Conditional Formatting formula to highlight the CONTACT NAME column if the number in the NAME WORD COUNT column is equal to 3 and if the text in the NAME FIX column is contained in the CONTACT NAME column. I really appreciate any help on this, its one of those that we all get stuck on sometimes, but I am sure has a simple solution.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
959
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Book1
ABC
1Contact NameName Word CountName Fix
2John John Doe3John Doe
3Bear Bear Joe3Bear Joe
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A3Expression=AND($B2=3;ISNUMBER(FIND($C2;$A2)))textNO


Note the formula is case sensitive. If that's not required, this can be used instead
Excel Formula:
=AND($B2=3;ISNUMBER(SEARCH($C2;$A2)))
 
Solution

mminchev

New Member
Joined
Feb 16, 2018
Messages
16
Book1
ABC
1Contact NameName Word CountName Fix
2John John Doe3John Doe
3Bear Bear Joe3Bear Joe
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A3Expression=AND($B2=3;ISNUMBER(FIND($C2;$A2)))textNO


Note the formula is case sensitive. If that's not required, this can be used instead
Excel Formula:
=AND($B2=3;ISNUMBER(SEARCH($C2;$A2)))
Worked great, thank you for your help!
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
959
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Most welcome. Thanks for the feedback.
 

Forum statistics

Threads
1,141,284
Messages
5,705,493
Members
421,398
Latest member
Rahat Anwar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top