Conditional Formatting

Hi Experts,

I would like to request Experts help create a conditional formatting to cross check data in column B (Source_ sheet) with data in column B (Data_sheet). The data from Source sheet (Column B)  are generally listed at the end of the list in column B in the Data Sheet.

If the data on the source sheet (Column B) not available at Data sheet, the cell need to highlight with "Red" color. Please ignore data starts with "uration" and "------" during crosschecking process. Hope Experts could help me create this feature.

 
Track-Data.xls
CartilloAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
In Excel 2010, I was able to create a formula-based CF rule for Source!B1:B457 using:

=AND($B1<>"",$B1<>"------------",$B1<>"uration",COUNTIF(Data!$B:$B,"*"&$B1&"*")=0)

That will NOT work in previous versions; before Excel 2010, a CF formula cannot refer to another worksheet.

In Excel 2007, and presumably earlier versions, this is a workaround:

1) Create a named range, DataB, referring to Data!$B:$B

2) Change the CF formula to:

=AND($B1<>"",$B1<>"------------",$B1<>"uration",COUNTIF(DataB,"*"&$B1&"*")=0)
0
 
CartilloAuthor Commented:
Hi,

Thanks a lot for the help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.