MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Highlight Matching

April 18, 2017 - by Bill Jelen

Highlight Matching

You have data in Excel. When you type a name, you want all of the cells that include that name to light up in another color. This episode will show you how.

Watch Video

  • There are videos on YouTube for fixing a STUCK CAPS LOCK Key
  • How to highlight all cells that contain a name that you type
  • Figure out where you will type the name
  • Select all the cells that potentially contain a match
  • Home, Conditional Formatting, New Rule, Highlight Cells that Contain,
  • Specific Text, Contains, $E$1, Click OK, OK
  • If you need to have it be an exact match, change Specific Text to Equals to

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2078 highlight the matching cell
  • alright today's questions and in as a
  • comment on YouTube is there any way when
  • I type of particular name and 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 was 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 right 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
  • gonna do up here in cell a1 is what
  • we're going to type what we're looking
  • for we won't have everything that says
  • Apple to get updated I'm going to choose
  • all the potential places where the
  • matches are going to be I select those
  • and we're going to go to conditional
  • formatting new rule new rule and in the
  • new rules we're going to format only
  • cells that contain i'm going to say in
  • this first drop-down i want to look for
  • specific text containing all right and
  • it's going to be containing cell II one
  • now right here you want to make sure
  • that puts the dollar signs in I mean so
  • that's an absolute reference then we'll
  • choose some sort of a format here there
  • you can't easily make things / 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 right so we
  • type 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 the only highlight the
  • things that were just apple and not
  • applesauce we'd have to go to
  • conditional formatting manage the rule
  • edit the rule and change this to say
  • that the cell value is equal to
  • e 1 exactly equal to e 1 click ok click
  • OK and then the applesauce will go away
  • so depending whether you want an exact
  • match are 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 all
  • right all sorts of tips like this are my
  • new book power except 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 naming you type line up find
  • out where you're going to type the name
  • in this case it was eone you might be
  • timing 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 e one
  • will put the dollar signs in for you
  • click OK now choose a pill click OK
  • click OK if you need to 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 a specific text 2 equals 2
  • all right there you go thanks for
  • sending that screaming question and
  • thanks to everyone else for stopping by
  • we'll see you next time for another
  • NetCast from MrExcel

Download File

Download the sample file here: Podcast2078.xlsm

Title Photo: AlexanderStein / Pixabay

Bill Jelen is the author / co-author of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.