I use the following Procedures to find non existing values based on two sheets and highlight them :
Sub DoIsNa(ResultRange As Range, ComparisonRange As Range)
Application.DisplayAlerts = False
Dim c As Range
For Each c In ResultRange
If IsNumeric(Application.Match(c, ComparisonRange, 0)) Then
Else: c.EntireRow.Interior.ColorIndex = "3"
Application.DisplayAlerts = True
Dim wb As Workbook
Dim ws1 As Worksheet
Set wb = ThisWorkbook
Set ws1 = wb.Sheets("Sheet1")
Set ws2 = wb.Sheets("Sheet2")
lastRows1 = ws1.UsedRange.Row - 1 + ws1.UsedRange.Rows.Count
lastRows2 = ws2.UsedRange.Row - 1 + ws2.UsedRange.Rows.Count
DoIsNa Range("Sheet1!A2:A" & lastRows1), Range("Sheet2!B2:B" & lastRows2)
I would like to optimize the procedures by adding some requirements:
1-I would like to add a Config Sheet in order to dynamically use this procedures based on the information reported in Config Sheet:
: In this sheet I will report all the values (highlight the values) which are not in ComparisonRangeSheet.
: This is the reference sheet, based on this I will report the values which don't exist in ResultRangeSheet
: In this sheet I need to report the various lines which are not in ResultRangeSheet highlighted in Red.
: Is the column letter related to my key to compare.
: Is the column letter related to my key reference.
Ex:ResultKey:B and ComparisonKey:C. I need to compare all the values reported in column B of ResultRangeSheet which are not in values reported in columns C of ComparisonRangeSheet.
2-I would also like to report concatenated key columns.
Ex: If I report for ResultKey C&E and ComparisonKey A&B the keys values will be composed by the concatenation of C&E and A&B. If this concatenation way is not the best way to report in ConfigSheet, I am in to change it.
3-My previous macro just highlight all the isna values I will like to highlight the values but also report them based on the Config Sheet DestinationResultSheet.
Please find attached a sample of I want.
Thank you very much for your help for your help.