You can also apply the VLOOKUP function to match values in two worksheets, and then apply conditional formatting based on the VLOOKUP and matching results in Excel. Please do as follows: Note: Drag the y scroll bar to view above picture. Conditional Format to compare two worksheets Posted on October 8, 2008 by Ken Puls I received an email today, asking how to compare two worksheets for differences. Of course, we could easily reach to VBA to do this, but we can also use Conditional Formatting to make these kind of comparisons. I figured that I'd knock up a quick example, so ... Comparing two lists in Excel is an easy task using a conditional formatting expression and color to visually identify the differences. Comparing data isn't always a common task, but in Excel, it's ...

Apr 08, 2016 · Perhaps a suggestion on how to abuse names ranges in conditional formatting. I usually mix them with a vlookup and format formula. (Named ranges are just references that can be used in them) If the value of the cell is in the range, I format the cell. e.g. =ISNA(VLOOKUP(A1, MyNamedRange, 1, false))=FALSE