Dec 06 2021 02:16 PM
Dec 06 2021 02:28 PM
Let's say the cells you want to highlight are in D2:D100, and the cell to compare to is the cell in the same row in E2:E100.
Select D2:D100. D2 should be the active cell in the selection.
On the Home tab of the ribbon, select Conditional Formatting >New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
Specify the desired formatting.
OK your way out.
If you want to compare to E2 for all cells in D2:D100, change $E2 to $E$2 in the formula.
And if you wanted the opposite, change < to >.
Dec 08 2021 12:15 AM
@Hans Vogelaar Many thanks for taking the time to reply. I understand your response and have adapted for my scenario.
In the table below I am now trying to compare the cells in each columns with that columns Median. Highlighting any cell that is above/below the median figure by 10%. Conditional formatting keeps reverting back to absolute values and therefore I am unable to drag formula.