kevin lazell
Well-known Member
- Joined
- Jun 2, 2004
- Messages
- 513
hi guys i have failed miserably to get this to work i have attatched a snap shot of my worksheet with intended result being the 3rd box
what i need code to do is lookup first name in cell a2 and check to see if it is in b2 if it is then check if interior color in c2 matches c1 it does not
so then checks d2 and d1 now this matches so j2=j2+1 now we go back and check if a2 is in b3 then a2 is in b4 etc then the code will start all over again
now checking 2nd name in cell a3 with cell b2 and b3 and b4 and so on
i think the main problem is in checking the name when using key f8 to step through the code and hover over fnd it displays the four names
Dim Fnd As Range
Dim a, x, name As Variant
a = 2: name = 2: x = 0
again:
x = x + 1
Set Fnd = Range("b:b").Find(Range("a" & name).Value, , , xlPart, , , False, , False)
If Fnd Is Nothing Then a = a + 1: GoTo again:
If Range("c" & a).Interior.ColorIndex = Range("c1").Interior.ColorIndex Then Range("i" & a) = Range("i" & a) + 1
If Range("d" & a).Interior.ColorIndex = Range("d1").Interior.ColorIndex Then Range("j" & a) = Range("j" & a) + 1
If Range("e" & a).Interior.ColorIndex = Range("e1").Interior.ColorIndex Then Range("k" & a) = Range("k" & a) + 1
If Range("f" & a).Interior.ColorIndex = Range("f1").Interior.ColorIndex Then Range("l" & a) = Range("l" & a) + 1
If Range("g" & a).Interior.ColorIndex = Range("g1").Interior.ColorIndex Then Range("m" & a) = Range("m" & a) + 1
a = a + 1
If x = 60 Then Exit Sub
If a = 12 Then a = 2: name = name + 1
GoTo again:
this is as far as i have got with the code a bit messy i know but just toying with it at the moment
any help will be greatly appreciated tia
what i need code to do is lookup first name in cell a2 and check to see if it is in b2 if it is then check if interior color in c2 matches c1 it does not
so then checks d2 and d1 now this matches so j2=j2+1 now we go back and check if a2 is in b3 then a2 is in b4 etc then the code will start all over again
now checking 2nd name in cell a3 with cell b2 and b3 and b4 and so on
i think the main problem is in checking the name when using key f8 to step through the code and hover over fnd it displays the four names
Dim Fnd As Range
Dim a, x, name As Variant
a = 2: name = 2: x = 0
again:
x = x + 1
Set Fnd = Range("b:b").Find(Range("a" & name).Value, , , xlPart, , , False, , False)
If Fnd Is Nothing Then a = a + 1: GoTo again:
If Range("c" & a).Interior.ColorIndex = Range("c1").Interior.ColorIndex Then Range("i" & a) = Range("i" & a) + 1
If Range("d" & a).Interior.ColorIndex = Range("d1").Interior.ColorIndex Then Range("j" & a) = Range("j" & a) + 1
If Range("e" & a).Interior.ColorIndex = Range("e1").Interior.ColorIndex Then Range("k" & a) = Range("k" & a) + 1
If Range("f" & a).Interior.ColorIndex = Range("f1").Interior.ColorIndex Then Range("l" & a) = Range("l" & a) + 1
If Range("g" & a).Interior.ColorIndex = Range("g1").Interior.ColorIndex Then Range("m" & a) = Range("m" & a) + 1
a = a + 1
If x = 60 Then Exit Sub
If a = 12 Then a = 2: name = name + 1
GoTo again:
this is as far as i have got with the code a bit messy i know but just toying with it at the moment
any help will be greatly appreciated tia