About Us

gaba

Thanks Dave. Exactly what I needed it.

"Dave Peterson" wrote:

Maybe something like...

Option Explicit
Sub testme()

Dim myCell As Range
Dim newWks As Worksheet
Dim curWks As Worksheet
Dim LastRow As Long
Dim LastCol As Long
Dim iRow As Long
Dim iCol As Long
Dim oRow As Long
Dim myColorIndex As Long

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

myColorIndex = 8
oRow = 0
With curWks
With .UsedRange
LastCol = .Columns(.Columns.Count).Column
LastRow = .Rows(.Rows.Count).Row
End With

For iRow = 1 To LastRow
If .Cells(iRow, "A").Font.ColorIndex = myColorIndex Then
For iCol = 1 To LastCol '2 to lastcol????
If .Cells(iRow, iCol).Font.ColorIndex = myColorIndex Then
oRow = oRow + 1
newWks.Cells(oRow, "A").Value _
= .Cells(iRow, iCol).Value
End If
Next iCol
End If
Next iRow
End With
End Sub

gaba wrote:

I'm trying to find a value in a row based on the cell color

If colorIndex = 8 Then 'row loop
check the row and find the first cell with colorIndex = 8
If color = 8 is find then 'column loop
'copy cell value destination new cell
Keep going thru the row and find all cell with that color and copy
values
End if 'column loop

Else 'row loop
go to next row
End if 'row loop

The objective is to go through Column A and find the colored cells. Then
find on that cell row checking by columns if there are one or more colored
cells and copy the values to a new list.

Will a loop inside a loop do it?

As usual, any help will be appreciated. Specially today, I seem to be having
a brainless day...
--
gaba :)

--

Dave Peterson

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Find and IF statement	Atif	New Users to Excel	6	November 4th 09 04:40 PM
comlex Find statement	Farooq Sheri	Excel Discussion (Misc queries)	2	May 11th 09 05:18 PM
If statement to find NA()	Barb Reinhardt	Excel Worksheet Functions	1	March 31st 06 05:50 PM
If statement to find NA()	Barb Reinhardt	Excel Worksheet Functions	0	March 31st 06 05:49 PM
when a Find statement is successful	captbluefin[_7_]	Excel Programming	4	November 27th 03 06:15 PM

Menu

About Us