Hi All,

I wrote a vba macro to match cells in range 1 with range 2. I uses 4 input
boxes to collect from end users information like range 1 say A1:B11, range 2
say C1:H65536, first cell in range say A1, and lastly first cell in range 2
say C1. After that, i uses a function (=countif(.....,.....)=0) in
conditional formating to do a comparison between the two ranges. If there is
no match, it will color the cells yellow in both range.

But i faces difficulty populating the range input (by end users into the
input box) into my =countif function. I need advice on how to work this out.
The following is my macros code:

Sub Compare_two_Ranges()

Dim rngName As Range
Dim rngName2 As Range
Dim cellRef As Range
Dim cellRef2 As Range

'Key range 1..

On Error Resume Next
Set rngName = Application.InputBox(Prompt:="Please input range 1..", _
Title:="Input Data Range", Type:=8)
'Key range 2..

Set rngName2 = Application.InputBox(Prompt:="Please input range 2..", _
Title:="Input Data Range", Type:=8)
'Key cell 1..

Set cellRef = Application.InputBox(Prompt:="Please input first cell in
range 1..", _
Title:="Input Data Range", Type:=8)
'Key cell 2..

Set cellRef2 = Application.InputBox(Prompt:="Please input first cell in
range 2..", _
Title:="Input Data Range", Type:=8)


'Select range 1..
rngName.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF(rngName2,cellRef)=0"
Selection.FormatConditions(1).Interior.ColorIndex = 36

'Select range 2..
rngName2.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF(rngName1,cellRef2)=0"
Selection.FormatConditions(1).Interior.ColorIndex = 36
'Range("A1:B1").Select
End Sub