Several comments:

You have defined many variables that you never use. I think I've deleted most
of them. What's the relevance of the value in F6? What is oRow to be used for?
And LastRow? What about MyCell?

You talk about double-clicking on a cell in column A. You never check that
Target is in column A. Do you want this routine to run regardless of the cell
that you click, even E50?

You are inside a "With Target.Interior" block. Then you write
.ActiveCell

That means "Target.Interior.ActiveCell", which is nonsense. Cell interiors
don't contain cells.

You write ActiveCell.Select. Since this is a double-click event, and you can't
double-click on more than one cell at a time, the selected cell is the active
cell is Target. This line serves no purpose.

You go through the "For i" loop, always checking the color of the active cell
(i.e. the color of Target). You never change the active cell inside the loop.
If the color of the active cell is what you want, then you should get it once,
before you start the loop.

Is the definition of a "colored cell" one with an interior color or a colored
font? Your code checks the color of the FONT, not the color of the interior.

I suspect that what you intend is to check the interior color of each cell as
you move across the row on which you double-clicked. See below.

I don't see any place where you've assigned a value to iRow. Therefore this
line
ActiveSheet.Cells(50, "E").Value = .Cells(iRow, iCol).Value
will cause an error -- there's no row 0.

Where are the copied values to be placed?

As it stands, you keep copying the value from (presumably) Target's row to
cell E50. Each value overwrites the previous one.

Let's assume E50 is empty.

Do you want to copy the first value to E50, the next to E51, the next to E52,
etc?

Or do you want the 1st value in E50, the next in F50, then G50, etc?

Or do they go to row 50 in the same column from which they came? And on the
next double-click would they go to row 51?

You have more End If statements than you have If's

It's very confusing to start a With block after an If block, then write the
End With statement AFTER the End If.

Here is a "cleaned up" version, but it doesn't make sense WRT where you are
copying the values.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
'on double click change color to show selection
'go through columns and find desired (blue colored) values,
'copy them starting E50

Dim LastCol As Long
Dim iRow As Long
Dim iCol As Long
Dim myColorIndex As Long

If Not IsEmpty(Target) Then
myColorIndex = 8
With ActiveSheet.UsedRange
LastCol = .Columns(.Columns.Count).Column
End With

iRow = Target.Row '<<<<< IS THIS CORRECT?
With ActiveSheet
For iCol = 1 To LastCol
'I ASSUME YOU WANT TO CHECK THE COLOR OF THE CELL in column iCol
'DO YOU WANT TO CHECK THE FONT COLOR OR THE INTERIOR COLOR?
'If .Cells(iRow, iCol).Font.ColorIndex = myColorIndex Then
If .Cells(iRow, iCol).Interior.ColorIndex = myColorIndex Then
'THE NEXT LINE MAKES NO SENSE AS WRITTEN -- YOU ARE
'CONTINUALLY OVERWRITING THE VALUE IN E50
'WHERE ARE THE VALUES TO BE COPIED -- SEE COMMENTS ABOVE
.Cells(50, "E").Value = .Cells(iRow, iCol).Value
End If
Next iCol
With Target.Interior
If .ColorIndex = xlNone Then .ColorIndex = myColorIndex
End With
End With
Else
Target.Interior.ColorIndex = xlNone
End If

Cancel = True

End If

End Sub


On Thu, 28 Oct 2004 20:55:01 -0700, "gaba"
wrote:

I think that I have the right logic but the code is still not working.

The idea is when I double click on a cell (Column A) it changes color (8).
I'll like from there to loop through the columns (same row) to find which
cells are colored (8) and copy the value(s) on the same sheet (E50).

So each time the user double clicks a row in column A it would find the
corresponding values and copy them down.

any help/ideas more than appreciated....
Gaba

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
'on double click change color to show selection
'go through columns and find desired (blue colored) values, copy them
starting E50

Dim myCell As Range
Dim LastRow As Long
Dim LastCol As Long
Dim iRow As Long
Dim iCol As Long
Dim oRow As Long
Dim myColorIndex As Long
Dim element As Long

element = Range("F6").Value 'cell stores number of total elements

myColorIndex = 8
oRow = 0
With ActiveSheet
With .UsedRange
LastCol = .Columns(.Columns.Count).Column
End With
End With

If Not IsEmpty(Target) Then
ActiveCell.Select
With Target.Interior
If .ColorIndex = xlNone Then
.ColorIndex = 8
For iCol = 1 To LastCol '2 to lastcol
If .ActiveCell.Font.ColorIndex = myColorIndex Then
ActiveSheet.Cells(50, "E").Value _
= .Cells(iRow, iCol).Value
End If
Next iCol
Else
.ColorIndex = xlNone
End If

End With
Cancel = True

End If

End Sub