A conditional formatting formula highlights values in column B that also exist in column D.
The same thing happens in column D, a conditional formatting formula highlights values in common between column B and D.
Repeat above steps with column D, the formula is at the top of this article.
The COUNTIF function counts how many times value in cell B3 is found in cell range $D$3:$D$7. B3 changes to B4 when Excel moves on to next cell below, however, that is not the case with $D$3:$D$7.
The $ dollar signs make this cell reference locked, in other words, it doesn't change.
The Conditional Formatting in Excel interprets all numbers except 0 as TRUE so if the COUNTIF function finds a value twice and returns 2 doesn't matter, it still highlights the cell.
Compare two columns and highlight matches.xlsx
This blog article is one out of five articles on the same subject.
Compare two columns and show differences
Highlight duplicates in two columns
Compare two lists of data: Highlight records existing in only one list in excel
Filter common values between two ranges using array formula in excel
Compare two columns and highlight differences
Compare two lists of data: Highlight common records in excel
Counting conditionally formatted cells (vba)
Highlight dates in a date range
Highlight closest number
How to highlight MAX and MIN value based on month
Highlight duplicate records
Highlight every other row
Highlight duplicates in a filtered Excel defined table
Highlight lookup values
Excel Function Reference
You can click on headers to sort table for easy finding.
Leave a Comment
How to add a formula to your comment:
How to add VBA code to your comment:
[vb 1="vbnet" language=","]
How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.
You can contact me through this webpage
Mail (will not be published) (required)
Notify me of followup comments via e-mail
User Defined Functions
Posts in category