Highlight Column A Based on Column C Values in Excel
Question details
Highlight cells in Column A according to the corresponding values in Column C.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The worksheet has many rows and columns, and the user wants to format Column A when the value in Column C is greater than or less than 2.
- Observed behavior
- Each selected cell in Column A should be highlighted when the numeric value in Column C on the same row is above or below 2.
Identify the first data row and the last row you want to format. The example below assumes data starts in row 2 and extends through row 10000.
Create a formula-based conditional formatting rule in Excel
Use one rule to highlight Column A when the corresponding numeric value in Column C is not 2.
Select A2:A10000 in the worksheet, adjusting the range to cover your data. Do not include a header row unless you want it formatted.
Go to Home > Conditional Formatting > New Rule, then select Use a formula to determine which cells to format.
Enter =AND(ISNUMBER($C2),OR($C2<2,$C2>2)). Replace 2 in $C2 with the first row number of your selected range; leave the comparison values as 2.
Select Format, choose a fill color on the Fill tab, and select OK. Select OK again to create the rule.
Open Home > Conditional Formatting > Manage Rules and confirm that Applies to covers the intended cells, such as =$A$2:$A$10000.
Highlight Column A using Column C in WPS Spreadsheet
WPS Spreadsheet can apply a formula-based conditional formatting rule to Column A using values from Column C in the same row.
- 1. Open the spreadsheet: Open your workbook in WPS Spreadsheet and select the target cells in Column A, such as A2:A10000.
- 2. Add a rule: Go to Home > Conditional Formatting > New Rule and choose the option to use a formula to determine which cells to format.
- 3. Enter the formula: Enter =AND(ISNUMBER($C2),OR($C2<2,$C2>2)), changing the row in $C2 if your selection starts on a different row.
- 4. Set the formatting: Choose a fill color in Format, confirm the rule, and check its applied range in Conditional Formatting > Manage Rules.

Frequently Asked Questions
Why is a value of exactly 2 not highlighted?
The formula checks for values less than 2 or greater than 2, so 2 meets neither condition. To include 2, use a condition such as $C2<=2 or $C2>=2, depending on the result you want.
How do I highlight only values greater than 2?
In the formula-based rule, use =AND(ISNUMBER($C2),$C2>2). Match the row number in $C2 to the first row of the selected Column A range.
Why are the wrong rows being highlighted?
Check that the row number in the formula matches the first row in the Applies to range. In Excel, open Home > Conditional Formatting > Manage Rules to inspect both the formula and range.
Will blank cells in Column C trigger the rule?
No. The ISNUMBER($C2) part of the example formula excludes blank cells and text values.




