Highlight Excel Sheet Names That Match a Model Number with Conditional Formatting
Question details
Highlight worksheet names in a list when a designated cell on the corresponding worksheet contains the specified model number.
- Product
- Microsoft Excel workbook with conditional formatting
- Device & OS
- not provided
- Scenario
- A list of worksheet names is stored in I2:I61, the model number to find is in E12, and each listed worksheet stores its model number in cell C7.
- Observed behavior
- The worksheet name should receive the selected conditional format when its referenced C7 value matches the value in E12.
Ensure every entry in I2:I61 exactly matches an existing worksheet name, including spaces and punctuation. Confirm that the model number is in E12 and that each target worksheet uses cell C7 for its model number.
Create a conditional-formatting rule with INDIRECT
Use this method to automatically format each sheet name in the list when the C7 value on that worksheet matches the model number in E12.
The INDIRECT function converts the sheet name in each row into a reference to that worksheet's C7 cell. Conditional formatting then evaluates the referenced value against the fixed model-number cell.
INDIRECT requires valid worksheet names. In large workbooks or with many listed sheets, this volatile function can slow recalculation.
On the worksheet containing the sheet-name list, select cells I2:I61.
Go to Home > Conditional Formatting > New Rule, then choose the option to use a formula to determine which cells to format.
In the formula box, enter =COUNTIF(INDIRECT("'"&I2&"'!C7"),$E$12)>0. Keep I2 relative so the rule evaluates the sheet name in each row, and keep $E$12 absolute.
Select Format, choose a fill color, font color, or other style, and then click OK to save the rule.
Open Home > Conditional Formatting > Manage Rules and confirm that the rule applies to =$I$2:$I$61.
Highlight Matching Worksheet Names in WPS Spreadsheets
WPS Spreadsheets lets you create a formula-based conditional-formatting rule that checks cell C7 on every listed worksheet and highlights names whose model number matches E12.
- 1. Open the workbook in WPS Spreadsheets: Open the workbook, then go to the worksheet that contains the sheet names in column I and the lookup value in E12.
- 2. Select the target names: Select I2:I61, or select the actual range that contains your worksheet names.
- 3. Add a conditional-formatting rule: On the Home tab, click Conditional Formatting > New Rule and choose a formula-based rule.
- 4. Use the sheet-reference formula: Enter =COUNTIF(INDIRECT("'"&I2&"'!C7"),$E$12)>0, then choose the fill or font format you want to apply.
- 5. Save and confirm the rule: Click OK and use Conditional Formatting > Manage Rules to verify that the Applies To range is $I$2:$I$61.
Frequently Asked Questions
Why does the formula use I2 instead of $I$2?
I2 must remain a relative reference so conditional formatting changes it to I3, I4, and later rows as it checks each sheet name. The lookup cell is fixed as $E$12 so every row compares against the same model number.
Can worksheet names with spaces be used in this conditional-formatting formula?
Yes. The formula uses "'"&I2&"'!C7, which places apostrophes around each sheet name. This supports names such as Model 100 or January Parts.
What happens if a sheet name in column I is missing or spelled differently?
INDIRECT cannot create a reference to a nonexistent worksheet, so the rule may return an error for that entry. Correct the name in column I so it exactly matches the worksheet tab name.
How can I change the cell checked on every worksheet?
Replace C7 in the formula with the cell that contains the model number on each worksheet. For example, to check D10, use =COUNTIF(INDIRECT("'"&I2&"'!D10"),$E$12)>0.
Will INDIRECT affect workbook performance?
It can. INDIRECT is volatile and recalculates frequently, so a workbook with many worksheet names, rules, or formulas may become slower. Limit the Applies To range to the rows you actually use.
