Sound Card Driver Updating: [Solved] copy cells with specific color

Friday, September 23, 2016

[Solved] copy cells with specific color

copy cells with specific color

I need to copy cells within a range if the cell color is yellow or green.

range to evaluate = I4:K45 (on each sheet)

I am attempting to replace step 2...
if condition is met (cell colored green or yellow), then copy etc...

Sub consolidate()
'1.
Create a sheet entitled "consolidated" and make the last sheet in workbook.
'2.
On sheet 1, consolidate all data from A2:H50 and combine into one list (sorted in alphabetical order).
'3.
Paste consolidated list on "consolidated" worksheet starting at A1.
'4.
Repeat same steps for the first 5 (or next four) sheets, putting the data in each sheet into the next column.
'(Sheet 1 to Column A; Sheet 2 to Column B; Sheet 3 to Column C; Sheet 4 to Column D; Sheet 5 to Column E)
Dim dst As Worksheet, x As Long, y As Long
On Error Resume Next
Set dst = Sheets("Consolidated")
On Error GoTo 0
    If dst Is Nothing Then
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Consolidated"
    Set dst = Sheets("Consolidated")
    End If
    For x = 1 To 5
        For y = 1 To 8
            lastrow = dst.Cells(Cells.Rows.Count, x).End(xlUp).Row
            Sheets(x).Cells(2, y).Resize(49).Copy dst.Cells(lastrow + 1, x)
        Next
    dst.Columns(x).SORT Key1:=dst.Cells(1, x), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Next
End Sub

Keys to the Problem copy cells with specific color

Download Error Fixer for Free Now

Hi

I have changed the function so that you can test I4:K45

updated function :

Public Function ShowColor(cl As Range) As String
Dim colorNumber As Long
Dim c As Range
Dim temp As String
Application.Volatile
For Each c In cl
    colorNumber = c.Interior.ColorIndex
    Select Case colorNumber
      Case 6, 43
         temp = "Yes"
         Exit For
      Case Else
         temp = ""
    End Select
Next c
ShowColor = temp
End Function

In a extra column, use =ShowColor(I4:K4)
Fill down
Filter on Yes in the extra column
This filtered range should correspond to your criteria

Regards
JT

Make Sure that your System Meets the System Requirements

Microsoft Windows Requirements :

1 gigahertz (GHz) or faster 32-bit (x86) or 64-bit (x64) processor
1 gigabyte (GB) RAM (32-bit) or 2 GB RAM (64-bit)
16 GB available hard disk space (32-bit) or 20 GB (64-bit)

You need to double-check your computer's hardware configuration against the following Microsoft requirements. If your PC hardware is not up to par, then make sure you update where necessary before continuing.

Another Safe way to Fix the Problem: copy cells with specific color:

How to Fix copy cells with specific color with SmartPCFixer?

1. Click the button to download SmartPCFixer . Install it on your computer. Open it, and it will scan your computer. The junk files will be shown in the list.