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.

2. After the scan is finished, you can see the errors and problems which need to be fixed.

3. The Fixing part is done, the speed of your computer will be much higher than before and the errors have been fixed.


Related: How Can You Update & Download NVidia 6100, 6800 GS/XT Display Driver v.260.89 WHQL,Where to Download NVidia GeForce 8800 GT WHQL-certified driver v.196.21,How to Update & Download NVidia GeForce Go 7400 Display Driver v.260.19.12,[Solved] Download NVidia GeForce GTX 460M Driver v.331.82,Download NVidia Quadro Plex Model IV VGA Driver v.304.43 Certified,Way to Download RealTek RTL8100C(L) Driver v.5.01,Way to Download RealTek RTL8100E Drivers v.694,Way to Update & Download RealTek RTL8101L Auto Installation Program v.6.110 driver,Method to Update & Download RealTek RTL8111G PXE and RPL ROM code v.2.58 driver,How to Update & Download RealTek RTL8411B(N) Driver v.10.003,Best Way to Update & Download ASUS A53SV nVidia Graphics Driver v.8.17.12.6686,Method to Herunterladen ASUS K75VJ Intel Rapid Storage Technology Treiber v.11.6.0.1030,How Can You Update & Download ASUS CG8580 Intel Chipset Driver v.9.3.0.1019,Method to Update & Download ASUS K41VD Intel INF Update Driver v.9.1.1.1015,Way to Update & Download ASUS Pro70T NB Probe v.3.0.0026 driver
Read More: [Solution] connection refused in async,Convert text to columns,Tech Support: Copy Data from Various Spreadsheets,[Anwsered] Count Excel cells with italic font,Troubleshoot:CPU utilization spikes to 100% randomly,Closing Internet Explorer causes error,Compare 2 Tables and Calculate the PROFIT Faster,Computer goes to Windows Boot Manager after Start,Connect to one of my network computer remotely when don't allow exception box Marked,computer locking up randomly

No comments:

Post a Comment