Formatting Issues

Highlight Multiple Ingredient Matches in Excel Cells

Aamir Naveed AkramAamir Naveed Akram Nov 18, 2026 868 views

Question details

Highlight ingredients in Excel cells when they match entries in a list on another sheet.

Highlight Multiple Ingredient Matches in Excel Cells
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.
Before you start

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.

Solution 1Recommended

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.

1
Separate the ingredients

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.

2
Clean up extra spaces

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.

3
Name the comparison list

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.

4
Create the formatting rule

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.

Split ingredients into cells and use conditional formatting
Match behavior: COUNTIF matches the entire ingredient and is not case-sensitive. Change A2 in the rule to the active cell's address if your selected range starts elsewhere.
Format ingredient matches

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. 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. 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. 3. Name the list: On Sheet2, select the comparison list, click the Name Box beside the formula bar, enter IngredientList, and press Enter.
  4. 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.
Highlight whole-ingredient matches with conditional formattingOpen and save Microsoft Excel XLSX filesUse a familiar spreadsheet interfaceWork with spreadsheets using the free version of WPS Office
microsoft office alternative - wps office

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.