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