Help for VBAClosed

Question

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.

venkat1926 · Answer

"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

Raj · Answer

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.

Help for VBA

2 responses

Similar discussions

Subscribe To Our Newsletter!