Use Formula Logic Instead of Conditional Formatting Color in Excel
Question details
Use the criteria behind conditional formatting in an Excel formula instead of testing the displayed cell color.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Return a message or a default value based on values in columns K and L, and optionally highlight the corresponding cell in column S.
- Observed behavior
- A comparison using L5<$L$3 alone can treat a blank L5 as zero and return a message when it is not intended.
Identify the conditional formatting criteria and check that K3 and L3 contain the intended numeric thresholds. Confirm that S1 contains the default value and that row 5 is the first data row.
Recreate the Conditional Formatting Criteria in an IF Formula
Use the same logical conditions to return a message without testing the displayed color.
Standard Excel formulas cannot directly test a displayed color produced by conditional formatting. Instead, evaluate the conditions that produce the color.
The L5>0 condition excludes blanks, zero, and negative values from the L comparison.
Select S5 and enter =IF(OR(K5>$K$3,AND(L5>0,L5<$L$3)),"Look at!",$S$1).
Drag the fill handle from S5 down column S for the rows you want to evaluate. The row references change while K3, L3, and S1 remain fixed.
Enter or review values in K5 and L5. The formula returns "Look at!" if K5 exceeds K3 or if L5 is positive and below L3; otherwise, it returns the value in S1.

Highlight Column S Using the Same Logical Test
Apply a conditional formatting rule when column S should also be highlighted for matching rows.
Apply the Same Formula Logic in WPS Spreadsheets
Use WPS Spreadsheets to evaluate the same conditions in an IF formula and highlight matching results with conditional formatting.
- 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 2. Enter the formula: Select S5 and enter =IF(OR(K5>$K$3,AND(L5>0,L5<$L$3)),"Look at!",$S$1).
- 3. Fill the results: Drag the fill handle from S5 down to apply the formula to the remaining data rows.
- 4. Highlight matches: Select the result range, open Home > Conditional Formatting, and create a formula-based rule using =OR(K5>$K$3,AND(L5>0,L5<$L$3)). Choose a fill color and confirm.

Frequently Asked Questions
Can an Excel IF formula read a conditional formatting color?
Standard worksheet formulas do not directly read the displayed color produced by conditional formatting. Instead, put the rule's logical criteria inside IF, OR, or AND.
Why does a blank cell trigger my less-than comparison?
Excel can treat a blank referenced cell as zero in a comparison. Use AND(L5>0,L5<$L$3) if only positive values should count, or AND(ISNUMBER(L5),L5<$L$3) if zero and negative numbers should also count.
Why do the references to K3, L3, and S1 include dollar signs?
The dollar signs make $K$3, $L$3, and $S$1 absolute references, so they remain fixed when you fill the formula down. K5 and L5 change to match each row.
How do I highlight the cells that meet the formula criteria?
Select the result cells and go to Home > Conditional Formatting > New Rule. Choose a formula-based rule and enter =OR(K5>$K$3,AND(L5>0,L5<$L$3)) when row 5 is the first selected row.




