Highlight Matching

Learn Excel from MrExcel Podcast, Episode 2078: Highlight the Matching Cell

Alright, today's question sent in as a comment on YouTube: Is there any way, when I type a particular name, any cell with the same name should be highlighted - changing fill colors or somehow made to catch attention, blinking or flashing? 

Now, I will solve that problem but first, this person has written before and I really encourage you to go out to YouTube and just search.  There's many, many videos out there that show you how to fix a stuck Caps Lock key, alright?  You don't need to be screaming the question.  I understand it gets stuck sometimes.  These videos will show you how to fix that, so go check that out. 

Alright, so here's what we're going to do.  Up here in cell E1, is where we're going to type what we're looking for.  We want to have everything that says Apple to get updated.  I'm going to choose all the potential places where the matches are going to be, select those and we're going to go to Conditional Formatting and New Rule, New Rule.  And then the New Rules, we’re going to Format only cells that contain and I’m going to say in this first drop-down, I want to look for Specific Text, Containing, alright and it's going to be containing cell E1.  Now right here you want to make sure it puts the $ in.  It means that's an Absolute Reference.  And then we'll choose some sort of a format here.  You can't easily make things flash but you can choose some sort of a nice Fill color here, then maybe a White font to make it stand out, like that.  Click OK, click OK and it's working, alright.  So we typed Apple, anything that says Apple gets updated. 

Let's try something else.  Banana and BAM!  All of the Bananas will get highlighted.  If we type something that's not in there, Cilantro, then no harm, no foul.  It doesn't highlight.  Or if we type, let's type Apple again, find one of the Apples and we'll change it to Applesauce. Applesauce, see it still gets highlighted.

Now, if you wanted to only highlight the things that were just Apple and not Applesauce, we'd have to go in the Conditional Formatting, Manage the Rule, Edit the Rule, and change this to say that the Cell Value is equal to E1, exactly =E1, click OK, click OK.  And then the Applesauce will go away.  So depending whether you want an exact match or not, easy, easy way to do that. 

Oh, and let's just do this test.  If your Caps Lock key is stuck in the ON position, yes, it continues to work.

Alright, all sorts of tips like this are in my new book, Power Excel with MrExcel, the 2017 Edition, 617 Excel mystery solved.

Alright, topics in this episode: We start out talking about the stuck Caps Lock key, really common problem.  There's videos on YouTube that show you how to fix that.  How do I highlight all cells that contain a name that you type, find out, find out where you're going to type the name.  In this case, it was E1.  You might be typing it somewhere else.  Select all cells that will potentially contain a match: Home, Conditional Formatting, New Rule, Highlight Cells that Contain, Specific Text, Contains, click on E1, it will put the $ signs in for you, click OK, Choose a Fill, click OK, click OK.  If you need it to have it be an exact match, so you don't want Applesauce to get highlighted when you choose Apple, then change Specific Text, change the drop-down as Specific Text to Equals to.

Alright, there you go.  Thanks for sending that screaming question in and thanks to everyone else for stopping by.  We'll see you next time for another netcast from MrExcel.