View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Bob Umlas Bob Umlas is offline
external usenet poster
 
Posts: 301
Default Whats Wrong With This

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
"Tinkerbell" wrote in message
ups.com...
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