Highlight Matching Text in Two Excel Ranges with Conditional Formatting
Question details
Highlight job names when identical text appears in both the completed-jobs range and the job-header range.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants one conditional-formatting rule for each range rather than creating separate rules for individual job names.
- Observed behavior
- Matching nonblank job names in D5:D370 and AO1:XX1 should receive the same fill color automatically.
Confirm that the job names in both ranges use consistent spelling and do not contain unintended leading or trailing spaces. Select the exact target range before creating each conditional-formatting rule so the relative cell reference starts from the top-left cell.
Create paired COUNTIF conditional-formatting rules for both Excel ranges
Use this method to highlight every nonblank job name that exists in both ranges, including new matching names added later.
A separate conditional-formatting rule is required for each range because the active-cell reference differs. Both rules use COUNTIF to check whether the current job name appears in the other range.
Select D5:D370, then go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =AND(D5<>"",COUNTIF($AO$1:$XX$1,D5)>0).
Select Format, open the Fill tab, choose a light fill color, and select OK. Confirm that the Applies to range is =$D$5:$D$370, then select OK to save the rule.
Select AO1:XX1, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =AND(AO1<>"",COUNTIF($D$5:$D$370,AO1)>0).
Select Format > Fill and choose the same light fill color used for the first rule. Confirm that the Applies to range is =$AO$1:$XX$1, then select OK.

Highlight matching job names across two ranges in WPS Spreadsheet
WPS Spreadsheet supports formula-based conditional formatting, so you can use COUNTIF rules to identify job names shared by a completed-jobs list and a header range. Apply the same fill style to both ranges to make matching entries easy to review.
- 1. Open the conditional formatting menu: In WPS Spreadsheet, select D5:D370 and choose Home > Conditional Formatting > New Rule.
- 2. Set the first matching-text formula: Choose a formula-based rule and enter =AND(D5<>"",COUNTIF($AO$1:$XX$1,D5)>0).
- 3. Apply a fill color: Choose Format or Fill, select a light fill color, and save the rule for D5:D370.
- 4. Create the reverse-range rule: Select AO1:XX1, create another formula-based rule with =AND(AO1<>"",COUNTIF($D$5:$D$370,AO1)>0), and apply the same fill color.

Frequently Asked Questions
Why are blank cells being highlighted by my matching-text rule?
Use the nonblank check at the start of each formula: D5<>"" for the D5:D370 rule and AO1<>"" for the AO1:XX1 rule. This prevents empty cells from matching other empty cells.
Do I need a separate conditional-formatting rule for every job name?
No. The COUNTIF formula checks each cell against the opposite range, so one rule for D5:D370 and one rule for AO1:XX1 covers all job names automatically.
How can I make the highlighted job names use the same color in both ranges?
When creating the second rule, choose the same option under Format > Fill that you selected for the first rule. The formulas can differ while the formatting style remains identical.
Will the rule detect new job names added to the ranges?
Yes, provided the new names are entered within the Applies to ranges. If you add jobs beyond D370 or outside AO1:XX1, edit the rule in Home > Conditional Formatting > Manage Rules and expand the applicable range.




