On Fri, 29 Oct 2004 06:04:05 -0700, "gaba"
wrote:

-F6 Contains the total number of rows to go through. Since I import a
different text file every time, I count the rows of elements to work with
once and keep it in F6.

Why? You don't use the row count in the code.

Since you want this to be a double-click routine, what the code does should be
related to the cell on which you clicked and other cells that have some
logical relationship to that cell (in your case, the relationship is that they
are in the same row).

If you want a macro that processes all rows, IMO it shouldn't be a
double-click event macro -- you would be putting code there that is unrelated
to WHERE you double-clicked.

-I'm looking to click the cell in column A and look through the columns for
the values that belong to that name:

Then you at the top of the procedure you should have some code like

IF Target.Column < 1 Then Exit Sub

Of course the interior color is not showing, but say I double click "Sodium"
it would go through the row and find <20 colored, 5498.761 colored. Get this
two values and put in order in E50, F50,etc.

Presumably you are going to double-click on the sheet more than once. And I
presume you would not want to over-write date you've already copied to row 50,
but put it in row 51.

You need code to find the last filled cell in column E, and save that in a
variable. You also need to keep track of what column you just copied to, so
you don't over-write E50, but put the next value in F50.

I'm surprised you don't also want to copy an information that identifies which
row and column the data came from, but then I don't understand your ultimate
purpose ...

Anyway, see if this does what you want.

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 at first empty cell at or below E50

Dim DestCol As Long
Dim DestRow As Long
Dim iRow As Long
Dim iCol As Long
Dim LastCol As Long
Dim myColorIndex As Long

With Target
'must click in column A, between rows 2 and 49
If .Column < 1 Or .Row < 2 Or .Row 49 Then Exit Sub
End With

If Not IsEmpty(Target) Then
myColorIndex = 8

With Target.Parent 'parent of a range is the worksheet

'determine where to put the copied values: find the
'last filled cell in column E and move down one row
DestCol = .Columns("E").Column
DestRow = .Cells(.Rows.Count, DestCol).End(xlUp).Row + 1
If DestRow < 50 Then DestRow = 50
DestCol = DestCol - 1 'back up one column for now

iRow = Target.Row
With .UsedRange
LastCol = .Columns(.Columns.Count).Column
End With

For iCol = 1 To LastCol
If .Cells(iRow, iCol).Interior.ColorIndex = myColorIndex Then
DestCol = DestCol + 1
.Cells(DestRow, DestCol).Value = .Cells(iRow, iCol).Value
End If
Next iCol

Target.Interior.ColorIndex = myColorIndex

End With
Else
Target.Interior.ColorIndex = xlNone
End If

Cancel = True

End Sub