Find cells containing specific text
A customer called me today to ask a question about Excel. I thought it was an interesting challenge, so I’ll share it with you here.
The customer has a list of part numbers and their components. When they select a component part number, they want to quickly find all parent part numbers that contain that component. The parent part number is in column A, and the component part number is in column B. Here’s an example:
Notice that the parent part number repeats in column A, and all of the associated components are listed in column B.
Take the first two rows, for example. Part number 15A24C is made up of two components: 15A and 24C. Note also that both 15A and 24C are used in other parent parts as well.
The customer’s ask was to click on a component in column B, and have all parts in Column A that use that component be highlighted. Here’s what that might look like:
In this example, I’ve selected cell B2, which has the component “15A”. I want to be able to see all parent parts that use 15A, so I’ve highlighted every parent that has a “15A” in in.
How did I do this?
Conditional formatting.
The method I ended up with required writing one line of VBA code, so before I show you that solution, let me demonstrate a method that doesn’t require any code.
To avoid writing any code, I had to utilize another cell on the worksheet, which I think of as a query cell. The user types in the component part number there, and all associated parent part numbers are highlighted. Here’s what that looks like:
The concept is this: The user types a component part number in cell F1, and all parents with that component are highlighted in column A.
For the conditional formatting, I used the “Specific Text” rule with the “Containing” constraint:
Notice that I have dollar signs in the cell reference (=$F$1), meaning I’m using an absolute reference. No matter which cells I assign this rule to, it’ll always compare the cell to F1. I've applied this rule to all cells in column A.
Remembering back to the original ask, the caller wanted to be able to click a value in column B, rather than having to type the value in a separate cell. In order to make that happen, I had to add one simple line of code to the Visual Basic window:
Here you can see that I added the code to the Worksheet_SelectionChange event. This procedure will run every time you change the selected cell, either by clicking a cell or by using the arrow keys on your keyboard.
As you can see, I am simply copying the value of the active cell (“Target.Value”) to cell F1. Then my conditional formatting takes over and highlights the appropriate cells.
The only problem with this is that if I click on a cell that’s not in column B, it’ll pick up the value of that cell. This is not a problem if I select a blank cell, but if I select a cell in column A, the behavior is a little unexpected:
Here I clicked on cell A5. Notice that “16B24C” was copied into cell F1, and all cells in column A that contain “16B24C” are highlighted. That’s probably not a big deal, but I could expand my code a little bit so that it’ll only react to clicks in column B. That would look like this:
I’ve added a simple If statement to check to see if the target cell (which is the one that is becoming active) is in column 2. Note that I have to check the column number instead of the letter.
With this addition, cell F1 will be set to the value of whichever cell I click in column B, but not if I click in any other column.
I hope you can get some use out of this technique. As always, reach out to me if you have any questions.
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© North Port Solutions, LLC, 2022. All rights reserved.