Forum Discussion
Cond. format: each cell in one col based on same row in another col
Select the range you want to format conditionally, say D1:D100.
The active cell in the selection should be the topmost cell, in this example D1.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop-down set to 'Cell Value'.
Select 'not equal to' from the second drop-down.
In the box next to it, enter the formula =M1 where M is the column you want to compare to and 1 is the row number of the active cell.
Click Format...
Activate the Font tab.
Select a font color.
Click OK, then click OK again.
Select the range you want to format conditionally, say D1:D100.
The active cell in the selection should be the topmost cell, in this example D1.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop-down set to 'Cell Value'.
Select 'not equal to' from the second drop-down.
In the box next to it, enter the formula =M1 where M is the column you want to compare to and 1 is the row number of the active cell.
Click Format...
Activate the Font tab.
Select a font color.
Click OK, then click OK again.
- HansVogelaar
Thank you VERY MUCH for your rapid, totally clear and unambiguous, and above all, SUCCESSFUL solution to my very first post on this forum.
I'll be able to use this trick time and again now that you have enabled my 82-year-old brain to get some sort of a handle on the significance of the term "active cell" in a range.
Bless you, Sir.
And NikolinoDE. and @Waqaar: I much appreciate you taking the time to reply.
This community forum just amazes me -- wish I'd known about it decades ago...
