Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop through column to find value
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 -- gaba :) |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop or Find code ?? need help !! | Excel Worksheet Functions | |||
Find loop doesn't loop | Excel Discussion (Misc queries) | |||
Find & loop in VBA | Excel Discussion (Misc queries) | |||
Find loop | Excel Programming | |||
Find value loop not working | Excel Programming |