Thread
:
Match cells in Range 1 with cells in Range 2
View Single Post
#
3
Posted to microsoft.public.excel.newusers
Don Guillett
external usenet poster
Posts: 10,124
Match cells in Range 1 with cells in Range 2
add this line below x= so you can enter as a2:a4 and a1
x = Application.ConvertFormula(x, xlA1, xlA1, xlAbsolute)
--
Don Guillett
SalesAid Software
"Don Guillett" wrote in message
...
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