Learn how to make a rule dependent on the content of another cell using conditional formatting in Excel!
So far, we have processed only and exclusively situations where the color of the given cell was specifically determined by the relationship of the values in a given range.
What will happen if we would like to introduce a new variable?
Conditional formatting enables us to determine highlights based on the value of a referred cell.
How does this look in practice? In the referred cell, we can modify the values arbitrarily. If the value changes, then the highlight of the given range (icons, shapes, custom formatting, etc.) will dynamically change also.
Let’s see a specific example!
In this case, I need to highlight all the dates in column N to be highlighted when they are more than 15 days newer than the date I enter in cell M2. Select the Updated column (range N2:N24) in the sample file.
Steps to make a rule dependent on the content of another cell
Let us see how to use rule which depend on another cell:
- Click Conditional Formatting on the Home tab of the ribbon
- Click New Rule. A popup window will appear.
- Choose ‘Use a formula to determine which cells to format’.
- Enter the formula =$M2>$N$1+15
- Click the Format button and select your formatting style.
- Click OK.
Explanation of formula:
In this expression, we evaluate values in column ‘M.’ M2 is a reference to the first cell in the selected range. We can apply this conditional formatting rule for this range. We should use the ‘$’ symbol for M2. Press F4 twice ($N$1) because N1 is an absolute value. The conditional formatting rule always uses this cell. What’s the meaning of the second part of this expression ‘>$N$1+15’? Every cell that is more than 15 days after the date in cell N1 is appropriate to our criteria! Check the result below: the highlighted cells are more than 15 days after the date in cell N1.