Unmatched Data in Cells between Sheet1 and Sheet2 Should be highlighted with colors

[BasavarajNC]

Dear Sir/Madam,

I am kindly requesting you to help me particularly in this section......I have 2 Sheets in Book1 File.. Sheet1 has 6 Columns with data and 39 Rows With data and sheet2 has 6 Columns with data and 41 Rows with data.I want to find out differences one (Not Common) between these two Sheets Sheet1 and Sheet2.

Could you kindly Explain Formula In detailed way.. as early as possible

Sincerely Thanking you,
Basvaraj,

 

[mumps]

Do you want to highlight all the cells in Sheet1 that are not in Sheet2 and all the cells in Sheet2 that are not in Sheet1?

 

[BasavarajNC]

Do you want to highlight all the cells in Sheet1 that are not in Sheet2 and all the cells in Sheet2 that are not in Sheet1?

Dear Sir/Madam,

It is my Gratitude to you for giving me immediate Reply.. Very Very Thankful to You .


Yes, Exactly Correct. If you allow me for that file for attaching over here.. I would do that..


Actually and Really wanted to say one thing.. That is .. I am very frustrated on myself and burning my head becuase of this...


It is My Very Humbel Request to Every One...


Please Do help kindly....


Sincerely Thanking You and All,


Basavaraj, From India

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this does what you want.
In Sheet1
- Select from A1 to F39
- Home ribbon tab -> Conditional Formatting -> New rule... -> Use a formula to determine which cells to format -> Format values where this formula is true:
=AND(A1<>"",COUNTIF(Sheet2!$A$1:$F$100,A1)=0) -> Format... -> Fill tab -> Choose your colour -> OK -> Ok

Do a similar thing on Sheet2 but swap the sheet name in the formula.

 

[mumps]

If you want a VBA option, this macro should do what you want:

Sub CompareSheets()
    Application.ScreenUpdating = False
    Dim Rng As Range, RngList As Object
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each Rng In sheeets("Sheet1").Range("A1:F39")
        If Not RngList.Exists(Rng.Value) Then
          RngList.Add Rng.Value, Nothing
        End If
    Next Rng
    For Each Rng In sheeets("Sheet2").Range("A1:F41")
        If Not RngList.Exists(Rng.Value) Then
          Rng.Font.ColorIndex = 3
        End If
    Next Rng
    RngList.RemoveAll
    For Each Rng In sheeets("Sheet2").Range("A1:F41")
        If Not RngList.Exists(Rng.Value) Then
          RngList.Add Rng.Value, Nothing
        End If
    Next Rng
    For Each Rng In sheeets("Sheet1").Range("A1:F39")
        If Not RngList.Exists(Rng.Value) Then
          Rng.Font.ColorIndex = 6
        End If
    Next
    RngList.RemoveAll
    Application.ScreenUpdating = True
End Sub

 

[BasavarajNC]

Welcome to the MrExcel board!

See if this does what you want.
In Sheet1
- Select from A1 to F39
- Home ribbon tab -> Conditional Formatting -> New rule... -> Use a formula to determine which cells to format -> Format values where this formula is true:
=AND(A1<>"",COUNTIF(Sheet2!$A$1:$F$100,A1)=0) -> Format... -> Fill tab -> Choose your colour -> OK -> Ok

Do a similar thing on Sheet2 but swap the sheet name in the formula.

-----------------------------------------------------------------------------------------------------------------

-Solved and Got Successful Result.

Dear Sir(Peter_SSs),

It is My Humble & Immense Gratitude to you..

I am sorry for taking long time to answer....sorry again,
I have implemented this formula on my Work Sheet and got successful Result as I wanted..

Once Again, Sincerely, Honestly, Whole-Heartily I am thankful to you Sir,

Best Regards,
Basavaraj, From India,

 

[BasavarajNC]

If you want a VBA option, this macro should do what you want:

Sub CompareSheets()
    Application.ScreenUpdating = False
    Dim Rng As Range, RngList As Object
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each Rng In sheeets("Sheet1").Range("A1:F39")
        If Not RngList.Exists(Rng.Value) Then
          RngList.Add Rng.Value, Nothing
        End If
    Next Rng
    For Each Rng In sheeets("Sheet2").Range("A1:F41")
        If Not RngList.Exists(Rng.Value) Then
          Rng.Font.ColorIndex = 3
        End If
    Next Rng
    RngList.RemoveAll
    For Each Rng In sheeets("Sheet2").Range("A1:F41")
        If Not RngList.Exists(Rng.Value) Then
          RngList.Add Rng.Value, Nothing
        End If
    Next Rng
    For Each Rng In sheeets("Sheet1").Range("A1:F39")
        If Not RngList.Exists(Rng.Value) Then
          Rng.Font.ColorIndex = 6
        End If
    Next
    RngList.RemoveAll
    Application.ScreenUpdating = True
End Sub

Dear Sir,

How to implement this VBA Option On work sheet... Where to write in my Excel Sheet..

Could you make little bit in Detail..Sir..

Heartily Thankful to You Sir,

Best Regards,

Basavaraj From India,

 

[Peter_SSs]

-Solved and Got Successful Result.

Dear Sir(Peter_SSs),

It is My Humble & Immense Gratitude to you..

I am sorry for taking long time to answer....sorry again,
I have implemented this formula on my Work Sheet and got successful Result as I wanted..

Great news! Thanks for letting us know.

How to implement this VBA Option On work sheet... Where to write in my Excel Sheet..

You could implement my previous method by VBA as follows.
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the 'HighlightUnmatched' macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Sub HighlightUnmatched()
  Dim lr1 As Long, lr2 As Long
  
  lr1 = Sheets("Sheet1").Columns("A:F").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
  lr2 = Sheets("Sheet2").Columns("A:F").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
  With Sheets("Sheet1").Range("A1:F" & lr1)
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(A1<>"""",COUNTIF(Sheet2!$A$1:$F$" & lr2 & ",A1)=0)"
    .FormatConditions(1).Interior.Color = vbYellow
  End With
  With Sheets("Sheet2").Range("A1:F" & lr2)
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(A1<>"""",COUNTIF(Sheet1!$A$1:$F$" & lr1 & ",A1)=0)"
    .FormatConditions(1).Interior.Color = vbYellow
  End With
End Sub