Forum Discussion
Cond. format: each cell in one col based on same row in another col
I'm useless at Excel -- but I have a 3000+ row sheet in which I'd like to be able to highlight with text
colour, where a cell in (say) col D differs from a cell in (say) col M in the same row.
I'm sure there's a simple way, but I've struggled for many hours without success, so I hope for enlightenment here...
Here's a SS of a faked dummy sheet which I hope shows the sort of thing I'm after:
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.
4 Replies
- Write Formula > B2 = D2 in Col F you will get result True or False then filter False and bingo there you go select all and colr them > shortcut
OR
Write If Formula in conditional formatting hoping you know it if not do let me know 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...
Conditional formatting can help make patterns and trends in your data more apparent.
Use conditional formatting to highlight information
ou can apply conditional formatting to a range of cells (either a selection or a named range), an Excel table, and in Excel for Windows, even a PivotTable report.
Hope I was able to help you with this information.
I know I don't know anything (Socrates)
