Whats Wrong With This

[Tinkerbell]

Public Sub FindTheGreen()
Dim c As Range
i = i + 1
For Each i In Range("A1:A1000")
If i.Interior.ColorIndex = 4 & Cells(i, 10).Value = 1 Then
i.CurrentRegion.Copy
Destination:=Range("AA1").Range("A65536").End(xlUp).Offset(1, 0)
End If
Next i
End Sub

Struggling Here need help I cant figure out the &

Every thing works except &Cells(I,10).Value=10
I simply need to select the Interior color of cell in col A and the
value in col J I cant figure this out can any one tell me how to do
this or do I need to do this a different way?

Thanks Debbie

 

[Bob Umlas]

First, you're using i as both a range and a value. For Each i In Range....
sets i as a range; i=i+1 uses it as a value.
Next, instead of "&", you need the word "AND". Try this:
Public Sub FindTheGreen()
For Each i In Range("A1:A1000")
If i.Interior.ColorIndex = 4 And Cells(i.Row, 10).Value = 1 Then
'<===note i.Row, not just i
i.CurrentRegion.Copy
Destination:=Range("AA1").Range("A65536").End(xlUp).Offset(1, 0)
'<===needs to be on same row
End If
Next i
End Sub

Bob Umlas
Excel MVP

I'm leading a FREE 1-hour online Webinar on Excel Tips & Tricks on Feb 13
and Feb 26 from 6-7PM est. If interested,
go to http://www.iil.com, click on the yellow/orange "Try a free webinar"
link
on the left side, click the Microsoft Excel Tips & Tricks link,
follow instructions to register.

You can also order my book on tips & tricks by visiting the site
http://www.iil.com/iil/excelmagic
Thanks.
Bob Umlas

 

[meatshield]

Public Sub FindTheGreen()
Dim c As Range
i = i + 1
For Each i In Range("A1:A1000")
If i.Interior.ColorIndex = 4 & Cells(i, 10).Value = 1 Then
i.CurrentRegion.Copy
Destination:=Range("AA1").Range("A65536").End(xlUp).Offset(1, 0)
End If
Next i
End Sub

Struggling Here need help I cant figure out the &

Every thing works except &Cells(I,10).Value=10
I simply need to select the Interior color of cell in col A and the
value in col J I cant figure this out can any one tell me how to do
this or do I need to do this a different way?

Thanks Debbie

Debbie,
A few things - First, you've declared c but you're not using it. You
haven't declared i and you're incrementing it as an integer and
assigning it a range.
I've cleaned up the code a bit and this works they way I think you've
intended it to:

Public Sub FindTheGreen()
Dim c As Range
Dim i As Long
i = i + 1
For Each c In Range("A1:A1000")
If c.Interior.ColorIndex = 4 And Cells(i, 10).Value = 1 Then
c.Copy Destination:=Range("A1").Range("AA65536").End(xlUp).Offset(1,
0)
End If
i = i + 1
Next c
End Sub
I'm not sure if you want i to initially be 1 or 0. This code loops
through the range A1:1000, checks to see if the cell is green and if it
is, checks to see that the cell 10(Did you want 9?) columns to the
right has a value of 1. if it does, the cell is copied to column AA
and placed one row below the last cell in that column.
Does it have to copy the cell from column A to column AA(and copy all
of the formatting), or do you just want the cell value? If you just
want the value, you can just set the AA range value equal to the A
range value. I think you could probably use offset instead of
cell(i,10) as well. The code I have in mind is as follows:

Sub FindTheGreen2()
Dim c As Range
For Each c In Range("A1:A1000")
If c.Interior.ColorIndex = 4 And c.Offset(0, 10).Value = 1 Then
Range("AA65536").End(xlUp).Offset(1, 0).Value = c.Value
End If
Next c
End Sub
You could use a with statement you you don't have to type c. for
interior, offset, and value too
Sub FindTheGreen3()
Dim c As Range
For Each c In Range("A1:A1000")
With c
If .Interior.ColorIndex = 4 And .Offset(0, 10).Value = 1 Then
Range("AA65536").End(xlUp).Offset(1, 0).Value = .Value
End If
End With
Next c
End Sub

I hope this helps, let me know if you have any questions.