OK, so the following scenario catches me every time: Conditional Formatting of cells based on values in a separate range of cells! In the example below, we have rows of data where we want the cells in one column (column B) to be formatted conditionally based on the values in a second column (column C). This is a Grade 12-level Excel question!
We want to highlight the months in column B where the month’s Sales figure in column C is below R 50,000.00.
- Select the range of cells to which the conditional formatting should apply
Click on the Conditional Formatting button and select the New Rule option from the list
- Select the Use a formula to determine which cell to format option
- Add the formula in the Format values where this formula is true: — pay special attention to the use of the $ to create an absolute column reference
- Select your preferred Format options (I have used an orange fill)
- Click on OK.
As mention in Step 3, the trick here is in the use of the absolute value $ symbol to lock the formula onto the column, but not the row.