Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Marking records with mismatched data
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Marking records with mismatched data
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Marking records with mismatched data
On May 12, 3:06 pm, merjet wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Marking data sets with non-blank cells | Excel Discussion (Misc queries) | |||
Delete records when certain records have duplicate column data | New Users to Excel | |||
mismatched data types | Excel Programming | |||
Compare List and Show Mismatched | Excel Worksheet Functions | |||
Type mismatched in Listbox | Excel Programming |