Match cells in Range 1 with cells in Range 2

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
See if this simpler approach helps. Notice NO selections

Sub docf()
r1 = InputBox("rng as $a$2:$a$4")
c1 = InputBox("cell as $A$1")
x = "=COUNTIF(" & r1 & "," & c1 & ")=0"
'MsgBox x
With Range(r1)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:=x
.FormatConditions(1).Interior.ColorIndex = 36
End With
End Sub
 
add this line below x= so you can enter as a2:a4 and a1
x = Application.ConvertFormula(x, xlA1, xlA1, xlAbsolute)
 
Back
Top