Conditional Formatting formula not working

NWPhotoExplorer

New Member
Joined
Jan 19, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello,

I am having issues with my conditional format formula. I am not sure what I did wrong, but it doesn't highlight anything. I was pretty sure I had things written out correctly, but I am not able to figure out this issue. I'm hoping someone else will see the error with the formula and can help me fix it.

Excel Formula:
=AND($H11="RF",OR(SEARCH("INFO PROVIDED",$J11),SEARCH("INFO REQUESTED",$J11),SEARCH("DESIGN",$J11)))

Basically, I want to look in the H column and if it shows RF then check the J column and see if it shows one of the above search terms. If it does, it highlights the entire row.

Thanks in advance!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,510
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Try

=AND($H11="RF",OR(ISNUMBER(SEARCH("INFO PROVIDED",$J11)),ISNUMBER(SEARCH("INFO REQUESTED",$J11)),ISNUMBER(SEARCH("DESIGN",$J11))))

M.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,657
Office Version
  1. 365
Platform
  1. MacOS
EDIT , answered as typing

Search returns an Value error , if not found , that would be the issue
Just as an alternative - You could also try using a countif () with a wild card
=AND($H11="RF",OR(COUNTIF($J11,"*INFO PROVIDED*"),COUNTIF($J11,"*INFO REQUESTED*"),COUNTIF($J11,"*DESIGN*")))
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

You need to Convert the SEARCH results to TRUE/FALSE:

Excel Formula:
=AND($H11="RF",OR(ISNUMBER(SEARCH({"INFO PROVIDED","INFO REQUESTED","DESIGN"},$J11))))
 

NWPhotoExplorer

New Member
Joined
Jan 19, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
=AND($H11="RF",OR(ISNUMBER(SEARCH({"INFO PROVIDED","INFO REQUESTED","DESIGN"},$J11))))
When I try this, I get this error message.
 

Attachments

  • Excel Error.png
    Excel Error.png
    3.3 KB · Views: 3

Watch MrExcel Video

Forum statistics

Threads
1,129,271
Messages
5,635,211
Members
416,847
Latest member
inaramos

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