[wirdip]

Hi,
I have a list of 150 stores of which 20 are designated "gold" which means they get better service for deliveries. In the spreadsheet that contains these stores they are already configured to have the name of the store blocked in yellow (gold). I have to manually retype these onto another sheet, is there a way of transferring this info using the colour as the argument.

Many thanks.

[JesseFerguson]

Are you coloring the field manually? If you aren't doing so already, you may want to set up a drop-down list with the "membership level" in it, then run a conditional format on that field to color the store cell automatically. If you've done that, you could just turn on autofilter, and only show the gold members. If you still need them on a separate sheet, you can set up a macro to filter, then copy and paste.

Hope this helps! Let me know if you need any help with details.

[bomb #21]

You don't state which version you're using ... although I'm not sure even 2007 has "the power" to filter by colour.

If you have:

- a "Sheet1" and a "Sheet2"

- the data in rows 1 to 150

- manual colouring in column A

- "records" with a width of 2 columns

, then a small macro such as below will copy the required ones from S1 to S2. So you could adjust it to fit your set-up.

If the colouring results from conditional formatting, you'd need to have the code test for the same condition(s) that creates the yellow (6) colour.

HTH

(Note: if you need help installing macros, post back)

Sub test()
For Each Cell In Sheets("Sheet1").Range("A1:A150")
If Cell.Interior.ColorIndex = 6 Then
Cell.Resize(, 2).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next Cell
End Sub

[JesseFerguson]

Rather than adding the conditional formatting to the second page, since you know you're only going to be pasting the Gold members, I'd have the second sheet pre-formatted, and just paste the values straight in.

And for the record, Excel 2007 does allow you to filter by color. But I'm not talking about that. I'm suggesting filtering based on the conditional field, ie:

Store|Location|Membership Level
004|Portland|Gold
005|Beaverton|Silver
003|Vancouver|Silver
010|Milwaukie|Gold

Set up your conditional formatting to key off column C (Membership Level), to color in any row where that cell has a value of "Gold." Then you just filter on the C column to only include "Gold" members

[bomb #21]

Not sure if you're saying I missed the point or you did.



I understand "I'm not talking about that. I'm suggesting filtering based on the conditional field". But if a membership level field was to be added, it would be more efficient (IMO) to populate it with a formula (if performance = OK, "Gold", else "silver") rather than use 2-option data validation that would need continual manual updating.

HTH

[JesseFerguson]

I'm not sure I'm following you. From reading his original post, it doesn't sound like the service level is listed anywhere on the sheet, but that they use the gold coloring as a quick reference to indicate who gets said service.

Here's an example of what I'm talking about. You could add whatever other columns you need, this gets the point.

[wirdip]

Thanks to everyone who helped with copying to another sheet based on colour of cells, in the end I used bomb21's macro worked a snip.

Can anyone tell me how to countif based on the colour red, I have a column of arrival times and if they are over 1 hour late they turn red, I need to keep a count of these.

Thanks in anticipation,

Alan

[Aj_old]

Originally Posted by wirdip

Thanks to everyone who helped with copying to another sheet based on colour of cells, in the end I used bomb21's macro worked a snip.

Can anyone tell me how to countif based on the colour red, I have a column of arrival times and if they are over 1 hour late they turn red, I need to keep a count of these.

Thanks in anticipation,

Alan

You can use countif function on the range that contains the time, and use the same condition as for coloring it in red!

[jimr381]

For clarification purposes Excel 2007 does indeed allow you to sort by color making it easier to find a certain color. I do not have it on my PC actively so I cannot tell you if you can filter by color though. Can someone clarify this for Bomb and myself please?

Originally Posted by bomb #21

You don't state which version you're using ... although I'm not sure even 2007 has "the power" to filter by colour.

If you have:

- a "Sheet1" and a "Sheet2"

- the data in rows 1 to 150

- manual colouring in column A

- "records" with a width of 2 columns

, then a small macro such as below will copy the required ones from S1 to S2. So you could adjust it to fit your set-up.

If the colouring results from conditional formatting, you'd need to have the code test for the same condition(s) that creates the yellow (6) colour.

HTH

(Note: if you need help installing macros, post back)

Sub test()
For Each Cell In Sheets("Sheet1").Range("A1:A150")
If Cell.Interior.ColorIndex = 6 Then
Cell.Resize(, 2).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next Cell
End Sub