Highlight Multiple Ingredient Matches in Excel Cells
Question details
Highlight ingredients in Excel cells when they match entries in a list on another sheet.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A cell contains several comma-separated ingredients that need to be compared with an ingredient list on Sheet2.
- Observed behavior
- Excel conditional formatting applies formatting to the whole cell rather than individual matching words.
Check where the ingredient cells and comparison list are located, and decide whether the ingredients must remain in one cell. If you split the text, make sure the columns to the right are empty because Text to Columns can overwrite them.
Split ingredients into cells and use conditional formatting
Use this approach for case-insensitive, whole-ingredient matches when each ingredient can occupy its own cell.
Select the comma-separated cells, then choose Data > Text to Columns > Delimited. Select Comma and finish the wizard so each ingredient has its own cell.
If the split cells contain leading or trailing spaces, use a formula such as =TRIM(A2) in an empty cell, fill it across and down as needed, and copy the results back using Paste Special > Values.
On Sheet2, select the ingredient list, such as A2:A100. Click the Name Box to the left of the formula bar, type IngredientList, and press Enter.
Select the separated ingredient cells, with A2 as the active cell. Choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =AND(A2<>"",COUNTIF(IngredientList,A2)>0), choose Format > Font, select red, and confirm.

Use VBA to color matching ingredients within each cell
Use a macro when comma-separated ingredients must remain in the same cell and partial, case-insensitive matches are acceptable.
Highlight separated ingredient matches in WPS Spreadsheet
Use WPS Spreadsheet to separate comma-delimited ingredients and apply a conditional formatting rule against a named list on another sheet.
- 1. Open and separate the ingredients: Open the workbook in WPS Spreadsheet. Select the ingredient cells and use Data > Text to Columns with Comma as the delimiter; ensure the destination columns are empty first.
- 2. Remove extra spaces: If needed, enter =TRIM(A2) in an empty cell, fill the formula across and down, then copy the results and use Paste Special > Values.
- 3. Name the list: On Sheet2, select the comparison list, click the Name Box beside the formula bar, enter IngredientList, and press Enter.
- 4. Apply a rule: Select the separated ingredients with A2 as the active cell. Under Home > Conditional Formatting, create a formula rule using =AND(A2<>"",COUNTIF(IngredientList,A2)>0), choose a red font, and confirm.

Frequently Asked Questions
Can Excel conditional formatting color just one word inside a cell?
No. Conditional formatting applies the format to the whole cell. To color individual ingredients within a text cell, use VBA or separate the ingredients into individual cells.
Will the conditional formatting rule match BHA regardless of capitalization?
Yes. COUNTIF is not case-sensitive, so the whole-ingredient rule matches BHA, bha, and Bha. Remove extra spaces with TRIM if needed.
Can the VBA macro find partial ingredient matches?
Yes. The example uses InStr with vbTextCompare to find a list entry anywhere within an ingredient, ignoring case. It colors the entire matching ingredient red.
Why didn't the macro remove red formatting after I changed the list?
The example only adds red formatting; it does not reset existing colors. Remove outdated formatting manually or modify the macro to reset formatting before it searches.
Can I run the VBA solution in Excel for the web?
No. Open the workbook in desktop Excel to add and run the macro, and save it as an .xlsm file.




