Displaying rows having one cell font color to different sheet.

[jlokesh16]

Hello,

I have 4 columns in sheet 1, in which the first column has cells whose font color is red.
I want to copy only those rows whose first column cell data has red font color to sheet 2.

 

[Peter_SSs]

Assuming Sheet2 exists with nothing in it, try this. (Check exact sheet names)
If it is not what you want, please give more details.

Sub GetRed()
  With Sheets("Sheet1").UsedRange
    .AutoFilter Field:=1, Criteria1:=vbRed, Operator:=xlFilterFontColor
    .Copy Destination:=Sheets("Sheet2").Range("A1")
    .AutoFilter
  End With
End Sub

 

[jlokesh16]

A	B	C	D
Apt name	Calls	start time	end time
ACH10P01	2	8/15/2018 11:51:49 AM	8/15/2018 12:23:53 PM
ALD10P01 <tbody> </tbody>	8 <tbody> </tbody>	8/15/18 12:36 PM <tbody> </tbody>	8/15/18 9:27 PM <tbody> </tbody>

<tbody>
</tbody>

Above is the table format i have.
In col A, there are cells having font color in red and black.
I want to only export the rows having red font color in col A to another sheet.

Expected display in other sheet should be as below

A	B	C	D
Apt Name	Calls	start time	end time
ACH10P01	2	8/15/2018 11:51:49 AM	8/15/2018 12:23:53 PM

<tbody>
</tbody>

 

[Peter_SSs]

That is exactly what the suggested code produced for me. In what way did it fail for you?

 

[jlokesh16]

it is only showing reference from col A, other three column data is not referenced.

 

[Peter_SSs]

it is only showing reference from col A, other three column data is not referenced.

Can you explain exactly what that means?

When I copied that data from the top table in post 3 to 'Sheet1' (cells A1:D3) in my test workbook and then ran the macro, 'Sheet2' (which was blank to start with) ended up exactly like the second table in post 3.

 

[jlokesh16]

I have assigned a button to the macro as "Export". When i click the button, only on column's data of single row is shown, data is there in almost 800 rows.

 

[Peter_SSs]

Ok here is another sample of mine.

Book1
	A	B	C	D
1	Apt name	Calls	start time	end time
2	ACH10P01	2	8/15/2018 11:51:49 AM	8/15/2018 12:23:53 PM
3	ALD10P01	8	8/15/18 12:36 PM	8/15/18 9:27 PM
4	data 1	2	Start time 3	End time 3
5	data 2	6	Start time 4	End time 4
6	data 3	5	Start time 5	End time 5
7	data 4	3	Start time 6	End time 6
8	data 5	2	Start time 7	End time 7
9	data 6	3	Start time 8	End time 8
10	data 7	1	Start time 9	End time 9
11	data 8	2	Start time 10	End time 10
12
Sheet1

Sheet2 is blank and I then run the macro I posted. My Sheet2 now looks like this:

Book1
	A	B	C	D
1	Apt name	Calls	start time	end time
2	ACH10P01	2	8/15/2018 11:51:49 AM	8/15/2018 12:23:53 PM
3	data 1	2	Start time 3	End time 3
4	data 2	6	Start time 4	End time 4
5	data 6	3	Start time 8	End time 8
6	data 7	1	Start time 9	End time 9
7	data 8	2	Start time 10	End time 10
8
Sheet2

If it is not working for you, perhaps you could post a sample workbook to a public file-share site (eg Dropbox) & provide a link to that file so that we can see if there is something significantly different with your workbook compared to mine.

 

[jlokesh16]

It is working now, will this code run for blue and green color too, as i have few records whose font color is blue and green, how do i extract them in separate sheets.

 

[Peter_SSs]

There are lots of different blues and greens (& reds) so you would need to identify the exact relevant colours and what sheet they should each go to. So the colours could be identified by a single number or as a RGB set. For example the one particular blue might be represented by the value 12611584 or as RGB(0, 112, 192)

Once we know the colours and the associated sheets we can construct the code.