Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Match cells in Range 1 with cells in Range 2
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find a range of values in a range of cells | Excel Worksheet Functions | |||
Conditional format if cell match found in another range of cells | Excel Worksheet Functions | |||
how to compute a range of cells based on another range of cells? | Excel Worksheet Functions | |||
how to compute a range of cells based on another range of cells? | Excel Worksheet Functions | |||
Count cells in one range based on parameters in another range | Excel Worksheet Functions |