macro to compare several columns with one reference column

[shawn3531]

Hello users,
I am a new user in VBA. I need to create a macro to compare several reference columns with one target column. This target column has the data's which are similar to the reference columns. My problem is I have a macro which can compare data's easily but highlight the target column with same color for every refernce column. My target is that the target column will highlight the data's but with different color for each refernce column. Moreover, the macro is can work only with the columns in the same sheet or with another sheet of the same workbook but not with the other workbook. I have attached the VBA code with this post.

Pardon me if my question looks wired.

Thanks in advance.

Sub Compare_Ranges()
'On Error Resume Next
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Dim rCell As Range
Dim result As Byte

'Turn off screen updating to speed up macro.
'Application.ScreenUpdating = False

Set rng1 = Application.InputBox(Prompt:="Enter range which you want to compare.", Title:="Criteria Range", Type:=8)
Set rng2 = Application.InputBox(Prompt:="Enter data range.", Title:="Data Range", Type:=8)
For Each rCell In rng1
rCell.Interior.ColorIndex = xlNone
rCell.Validation.Delete
result = WorksheetFunction.CountIf(rng2, rCell)
If result = 0 Then
rCell.Interior.ColorIndex = xlNone
ElseIf result = 1 Then
rCell.Interior.Color = vbGreen
With rCell.Validation
.Add xlValidateInputOnly
.InputMessage = "The value is " & result & "time occured in " & rng2.Address & "."
End With

ElseIf result = 2 Then
rCell.Interior.Color = vbYellow
With rCell.Validation
.Add xlValidateInputOnly
.InputMessage = "The value is " & result & " time occured."
End With

ElseIf result = 3 Then
rCell.Interior.Color = vbBlue
With rCell.Validation
.Add xlValidateInputOnly
.InputMessage = "The value is " & result & " time occured."
End With
ElseIf result = 4 Then
rCell.Interior.Color = vblavender
With rCell.Validation
.Add xlValidateInputOnly
.InputMessage = "The value is " & result & " time occured."
End With

End If
Next
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub