Help for VBA

[Closed]
Report
-
 Raj -
Hello,

This question is related to MS Office 7.

I want to write a code to validate codes used in transaction are present in master sheet or not. For example, there are 2 sheets for master and transaction so the codes used in transactions should get checked one by one whether present in master or not and if not matched with master, that codes should get marked with specific color in transaction sheet.

Pl let me know. Thanks in advance.

2 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
801
"codes used in transactions should get checked one by one whether present in master or not and if not matched with master, that codes should get marked with specific color in transaction sheet. "

little confusing . anyhow sample data in sheet "master" is like this

code heng2 heng3
a 26 55
s 99 64
d 70 16
f 4 91
g 50 86
h 78 6
j 84 77

data in "transaction" is like this

heng1 heng2 heng3
a 26 55
d 70 16
w 4 91
r 84 77

now try this macro

Sub test()
Dim r As Range, c As Range, cfind As Range, x
With Worksheets("transaction")
Set r = Range(.Range("A2"), .Range("A2").End(xlDown))
For Each c In r
x = c.Value
With Worksheets("master")
Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)
If cfind Is Nothing Then
GoTo nextstep
Else
GoTo nextc
End If
End With
nextstep:
Range(c, c.End(xlToRight)).Interior.ColorIndex = 3
nextc:
Next c
End With
End Sub
Thanks indeed for your prompt reply. In the mean time I have tried following code which working as desired

Master Sheet Codes
R001
R002
R003
R004
R005
R006

Transaction Sheet Codes
R001
R022
R003
R004
R001


Now here R022 should get marked with Red color since it is not present in Master codes. Following code works for this objective'


Set rngChk = Worksheets("Est_Cons").Range("C2:C100")
For Each cL In rngChk
If cL <> "" Then
With Sheets("Material_Master").Range("A:A")
Set rngChk1 = .Find(What:=cL, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not rngChk1 Is Nothing Then
Application.Goto rngChk1, True
curRow = ActiveCell.Row
Else
cL.Font.ColorIndex = 3
End If
End With
End If
Next cL

Please let me know in case this code has any further issuse.