Formatting Issues

Highlight Matching Text in Two Excel Ranges with Conditional Formatting

Jason MillerJason Miller Nov 12, 2026 869 views

Question details

Highlight job names when identical text appears in both the completed-jobs range and the job-header range.

Highlight Matching Text in Two Excel Ranges with Conditional Formatting
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.
Before you start

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.

Solution 1Recommended

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.

1
Add a rule for the completed-jobs 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).

2
Choose the matching fill format

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.

3
Add a rule for the job-header range

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).

4
Use the same fill color

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.

Create paired COUNTIF conditional-formatting rules for both Excel ranges
Matching names are highlighted automatically: The rules ignore blank cells and update whenever a job name is added, removed, or changed in either referenced range.
Work with matching job names in a spreadsheet

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. 1. Open the conditional formatting menu: In WPS Spreadsheet, select D5:D370 and choose Home > Conditional Formatting > New Rule.
  2. 2. Set the first matching-text formula: Choose a formula-based rule and enter =AND(D5<>"",COUNTIF($AO$1:$XX$1,D5)>0).
  3. 3. Apply a fill color: Choose Format or Fill, select a light fill color, and save the rule for D5:D370.
  4. 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.
Create COUNTIF conditional-formatting rules for matching text across separate ranges.Automatically highlight nonblank job names found in both lists.Open and edit Microsoft Excel .xlsx and .xls files with compatible spreadsheet formatting.Use a familiar spreadsheet interface for formulas, formatting, and range management.
microsoft office alternative - wps office

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.