Conditional formatting works great if you're looking to highlight rows based on data or values already contained in the sheet (e.g. a red background color can appear on rows when the "status" is "overdue"). Issues can arise when you want to apply the formatting based on a comparison between two cells in the sheet. For example, if you have a "target cost" column and an "actual cost" column, and want to apply a red background color to rows that exceed the estimate. This can't be done with conditional formatting rules alone.
That’s why we recommend using conditional formatting's greatest ally - formulas! Specifically, you will want to use an IF formula to perform the comparison between the two values, and then apply the formatting rule based on the result of the formula.
Here’s how to use conditional formatting and formulas together:
- Insert a new checkbox column into the sheet.
- In the top row, use an IF formula to check the box IF your criteria is met. To go along with our example above, here's a formula that would check the box if the "Actual Cost" exceeds the "Target Cost" in row 1: =IF([Actual Cost]1 > [Target Cost]1, 1, 0).
- Drag-fill the formula into all other rows in the column. Don't worry about adding the formula to new rows that are created -- Smartsheet's auto-fill feature will take care of this for you.
- Set up the conditional formatting rule to apply a red background color to rows if the box is checked.
For more information on conditional formatting and formulas, see our related blog tips:
- How to build a nested IF statement: http://www.smartsheet.com/blog/support-tip-build-nested-IF
- How to use the checkbox formula: http://www.smartsheet.com/blog/support-tip-checkbox-formula
- And a related help article: http://help.smartsheet.com/customer/portal/articles/775363-using-formulas