How to Highlight Excel Cells When a Number Exists on Another Sheet in WPS Office
Question details
The user wants to highlight open purchase order numbers in WPS Spreadsheets whenever the same number also appears on a closed purchase orders worksheet.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user manages purchase order data across two worksheets and needs to visually flag open POs that are already listed as closed.
- Observed behavior
- After applying the conditional formatting rule, any purchase order number that exists in the closed POs sheet is highlighted with the selected fill or font format.
Open the workbook in WPS Spreadsheets and confirm the exact sheet name and column range for the closed purchase order list. Make sure the range selected on the open orders sheet starts with the cell you use in the formula, such as A1, so the relative reference adjusts correctly.
Create a COUNTIF Conditional Formatting Rule in WPS Spreadsheets
Use this method to highlight open purchase order cells when a matching number exists in a closed purchase orders worksheet.
This formula-based rule checks each selected cell with COUNTIF and applies formatting only when the count in the closed POs column is greater than zero. Adjust the sheet name, source column, and starting cell reference to match your workbook structure.
Open your workbook in WPS Spreadsheets and go to the open orders worksheet. Select the range containing the purchase order numbers, starting from the top-left cell that matches the formula reference.
Click the Home tab, then click Conditional Formatting and choose New Rule.
In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
In the formula box, type =COUNTIF('Closed POs'!$A:$A,A1)>0. Replace Closed POs with the actual closed orders sheet name and $A:$A with the column to search, keeping the starting cell reference relative.
Click Format, choose the desired fill color or font style on the relevant tabs, then click OK to accept the format and OK again to apply the rule.
Highlight Matching Numbers Across Sheets with WPS Spreadsheets
Use WPS Spreadsheets' conditional formatting and COUNTIF to highlight purchase order numbers that appear on a closed orders worksheet. The rule updates automatically as data changes and keeps the workbook format compatible with Microsoft Excel.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office Spreadsheets and open the workbook containing the open and closed purchase order sheets.
- 2. Select the open purchase order range: On the open orders sheet, select the cells that contain the purchase order numbers.
- 3. Create a formula-based rule: Go to Home > Conditional Formatting > New Rule, select Use a formula to determine which cells to format, and enter =COUNTIF('Closed POs'!$A:$A,A1)>0.
- 4. Set and apply formatting: Click Format, choose a fill or font style, then click OK twice to apply the highlight rule.
- 5. Verify the highlighting: Enter a number that exists in the closed POs sheet into the open range and confirm it is highlighted automatically.
Frequently Asked Questions
Can I use this rule if the closed purchase order sheet has a different name?
Yes. Replace 'Closed POs' in the formula with your actual sheet name. If the sheet name includes spaces or special characters, keep it wrapped in single quotes, for example =COUNTIF('Closed Orders 2025'!$A:$A,A1)>0.
How do I highlight the entire row instead of only the purchase order cell?
Select the full data range before creating the rule, and use a formula like =COUNTIF('Closed POs'!$A:$A,$A1)>0. The $A1 reference locks the comparison to column A for each row while the rule applies formatting to the entire selected row.
What should I do if the highlight is not appearing?
Open the rule from Home > Conditional Formatting > Manage Rules, check that the sheet name and column range are correct, and confirm the active cell reference in the formula matches the top-left cell of the selected range. Also make sure the formatting fill or font is actually set.
Can I apply this conditional formatting to a whole column?
Yes. Select the entire column range such as A:A on the open orders worksheet and use the formula with the first cell relative reference A1. The rule evaluates each cell against the closed POs list and highlights matching values.
