Marking records with mismatched data

J

johan

Hello,

I hope somebody can help me out with this questions.

1th question:
In sheet1 there is a list with names in range B10 till B...(last used
record).
In this sheet there is also by a few names in kolom A10 till A...(last
used record) registrated the letter "O".
So we have for example:
A B
record 10 O Pietersen
record 11 Jansen
record 12 O Klaasen
record 13 Huisma

In sheet2 in range A2 till A.. (last used record) are also names
registrated.
I need a macro that coloring red the datafield in sheet2 if this name
is not on the list in sheet1 and if the name is registrated on the
list but there is no letter O registrated before it in kolom A it
should be coloring yellow.

2th question:
In a specific kolom (format "number" with 0 decimals) the user should
registrate a four digit number with the number format. If there is
registrated something differents (more or less numbers or a
dateformat) the datafield should be coloring red.

If somebody can help me........
Thanks a lot.

Regards,
Johan.
The Netherlands.
 
M

merjet

If I understood it correctly, the following answers your 1st question.
Your 2nd question wasn't clear enough for me, but you can probably
answer it yourself using the following as a guide.

Hth,
Merjet


Sub Macro1()
Dim c1 As Range
Dim c2 As Range
Dim rng1 As Range
Dim rng2 As Range
Dim iEnd As Long
Dim bFound As Boolean

iEnd = Sheets("Sheet1").Range("B65536").End(xlUp).Row
Set rng1 = Sheets("Sheet1").Range("B10:B" & iEnd)
iEnd = Sheets("Sheet2").Range("A65536").End(xlUp).Row
Set rng2 = Sheets("Sheet2").Range("A2:A" & iEnd)
For Each c2 In rng2
bFound = False
For Each c1 In rng1
If c1 = c2 Then
bFound = True
If c1.Offset(0, -1) <> "O" Then c2.Interior.ColorIndex = 6
End If
Next c1
If bFound = False Then c2.Interior.ColorIndex = 3
Next c2

End Sub
 
J

johan

If I understood it correctly, the following answers your 1st question.
Your 2nd question wasn't clear enough for me, but you can probably
answer it yourself using the following as a guide.

Hth,
Merjet

-------------------------------

THANKS Merjet,
It's the solution for both questions.

Regards,
Johan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top