Conditional Formatting based on substring -in the same column-

DisceCogetare

New Member
Joined
Jan 26, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
It's a common and simple question/answer to run Conditional Formatting due to a substring based on a string/cell outside of the column.
But how would I accomplish this within the column? Obviously I'd have to account for Cell <> Itself, but I can't even get the first half to work yet.
Long story short, I want to notify someone visually if they happen to add an entry to a list which happens to be contained in something else in the list.

A
1JumpHighlight A1 because Search(A1,A2)
2Jumping Jacks
3Jumping Around
4RunHighlight A4 because Search(A4,A5)
5Eating prunes
6PingHighlight A6 because Search(A6,A2)

First post, be gentle? ;)
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
=COUNTIF($A$1:$A$6,"*"&A1&"*")>1

Capture.JPG
 
Upvote 0
Welcome to the MrExcel board!

Here is another option.

22 01 27.xlsm
A
1Jump
2Jumping Jacks
3Jumping Around
4Run
5Eating prunes
6Ping
CF strings
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:A6Expression=COUNT(SEARCH(A1,A$1:A$6))-1textNO
 
Upvote 0
Solution
Thank you both, I hadn't considered wildcards or just counting the number of hits on a search.
 
Upvote 0
You're welcome. Glad we could help. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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
Back
Top