Thread
:
Match cells in Range 1 with cells in Range 2
View Single Post
#
2
Posted to microsoft.public.excel.newusers
Don Guillett
external usenet poster
Posts: 10,124
Match cells in Range 1 with cells in Range 2
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
--
Don Guillett
SalesAid Software
"Tan" wrote in message
...
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
Reply With Quote
Don Guillett
View Public Profile
Find all posts by Don Guillett