Search for cell color, copy values in other columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jamal - 24 Jul 2008 20:44 GMT

Hi everyone,

I am trying to put together a tool that can can search, column by column,
for a certain background/fill color (yellow), and then copy/paste the
value/text in various columns of the same row for each instance of yellow
background colored cells, into another worksheet.

I think I know how the first part of this problem can be solved (searching
for and identifying the background color cells), but how can I code to
copy/paste the values in specific cells?

Reply to this Message

Wigi - 24 Jul 2008 21:46 GMT

The code depends on a few things:

- how you set up the code to do the search process
- where the values are that you want to copy, and to where you want to copy
them

Signature

Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music

> Hi everyone,
>
[quoted text clipped - 6 lines]
> for and identifying the background color cells), but how can I code to
> copy/paste the values in specific cells?

Reply to this Message

ryguy7272 - 24 Jul 2008 22:41 GMT

This may, or may not, do what you want...there is a requirement that the cell
be colored by applying the 'Fill Color' tool (i.e., not conditional
formatting colors). Also, I am assuming that the cell's interior color is
red (i.e. 3)
http://www.mvps.org/dmcritchie/excel/colors.htm

Change the color to suit your needs:

Sub colorcopier()
Dim i As Long
k = 1
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.count + r.Row - 1
For i = 1 To nLastRow
If is_it_red(i) Then
Set rc = Cells(i, 1).EntireRow
Set rd = Sheets("Copy If Red #2").Cells(k, 1)
rc.Copy rd
k = k + 1
End If
Next
End Sub

Function is_it_red(i As Long) As Boolean
is_it_red = False
For j = 1 To Columns.count
If Cells(i, j).Interior.ColorIndex = 3 Then
is_it_red = True
Exit Function
End If
Next
End Function

Regards,
Ryan---

Signature

RyGuy

> The code depends on a few things:
>
[quoted text clipped - 12 lines]
> > for and identifying the background color cells), but how can I code to
> > copy/paste the values in specific cells?

Reply to this Message

Jamal - 25 Jul 2008 19:33 GMT

Hi Ryan,

Thanks for your reply, but I get a run time error 9 (Subscript out of range)
for the line:
Set rd = Sheets("Copy If Red #2").Cells(k, 1)

I modified the code to search for yellow, and it is as follows:

Sub colorcopier()
Dim i As Long
k = 1
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
For i = 1 To nLastRow
If is_it_yellow(i) Then
Set rc = Cells(i, 1).EntireRow
Set rd = Sheets("Copy if Yellow #2").Cells(k, 1)
rc.Copy rd
k = k + 1
End If
Next
End Sub

Function is_it_yellow(i As Long) As Boolean
is_it_yellow = False
For j = 1 To Columns.Count
If Cells(i, j).Interior.ColorIndex = 6 Then
is_it_yellow = True
Exit Function
End If
Next
End Function

Also, I believe the code you wrote would copy the entire row, but I am only
looking for specific cells to be copied (say, cells in column A, G, and M)

Any suggestions on how this can be done?

Thanks!

> This may, or may not, do what you want...there is a requirement that the cell
> be colored by applying the 'Fill Color' tool (i.e., not conditional
[quoted text clipped - 48 lines]
> > > for and identifying the background color cells), but how can I code to
> > > copy/paste the values in specific cells?

Reply to this Message

Ron de Bruin - 25 Jul 2008 01:06 GMT

Check out
http://www.rondebruin.nl/easyfilter.htm

If you use 2007 post back

Signature

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

> Hi everyone,
>
[quoted text clipped - 6 lines]
> for and identifying the background color cells), but how can I code to
> copy/paste the values in specific cells?

Reply to this Message

Jamal - 25 Jul 2008 04:24 GMT

Hi Ron,

That tool looks promising. Any suggestions if I am using xl07?

> Check out
> http://www.rondebruin.nl/easyfilter.htm
[quoted text clipped - 11 lines]
> > for and identifying the background color cells), but how can I code to
> > copy/paste the values in specific cells?

Reply to this Message

Ron de Bruin - 25 Jul 2008 08:54 GMT

Hi Jamal

I am working on a 2007 version

Signature

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

> Hi Ron,
>
[quoted text clipped - 15 lines]
>> > for and identifying the background color cells), but how can I code to
>> > copy/paste the values in specific cells?

Reply to this Message

Jamal - 25 Jul 2008 19:39 GMT

Ron,

I tried using Easy Filter in xl03, and I have a couple questions:

I. Is there a way to make the program automatically search row by row,
instead of having to use the program on each row individually?

II. The program copies each entire row to a new spreadsheet, but I am only
looking for certain cells to be copied (say the values in column A, G, and M,
for a given row). Is there a way to get this to work, too?

Thanks for your help!

> Hi Jamal
>
[quoted text clipped - 19 lines]
> >> > for and identifying the background color cells), but how can I code to
> >> > copy/paste the values in specific cells?

Reply to this Message

Ron de Bruin - 25 Jul 2008 21:52 GMT

Hi Jamal

Are you using normal colors or do you use Conditional formatting to color the cells

Signature

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

> Ron,
>
[quoted text clipped - 32 lines]
>> >> > for and identifying the background color cells), but how can I code to
>> >> > copy/paste the values in specific cells?

Reply to this Message