Copy/paste cell if Cell has it's a color Filled

[ashringg]

Hi Savvy People,

I'm trying to write a macro which would automatically:

1. Copy cells that have a fill color
2. Paste those filled color cells (with contents, the color but not the number format) to a list on the same sheet- is different column or section

Note: there multiple fill colors.

Would you be able to assist? Any tips would help.

Yearly Calendar Macro Template AD.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	2024
2	January '24								February '24								March '24								April '24								RGB Color
3	S	M	T	W	T	F	S		S	M	T	W	T	F	S		S	M	T	W	T	F	S		S	M	T	W	T	F	S		252, 213, 180	250, 191, 143
4		1	2	3	4	5	6						1	2	3							1	2			1	2	3	4	5	6		216, 228, 188	196, 215, 155
5	7	8	9	10	11	12	13		4	5	6	7	8	9	10		3	4	5	6	7	8	9		7	8	9	10	11	12	13		255, 255, 204	255, 255, 0
6	14	15	16	17	18	19	20		11	12	13	14	15	16	17		10	11	12	13	14	15	16		14	15	16	17	18	19	20		183, 222, 232	146, 205, 220
7	21	22	23	24	25	26	27		18	19	20	21	22	23	24		17	18	19	20	21	22	23		21	22	23	24	25	26	27		204, 192, 218	177, 160, 199
8	28	29	30	31					25	26	27	28	29				24	25	26	27	28	29	30		28	29	30						217, 217, 217	191, 191, 191
9																	31																221, 217, 196	196, 189, 151
10	May '24								June '24								July '24								August '24
11	S	M	T	W	T	F	S		S	M	T	W	T	F	S		S	M	T	W	T	F	S		S	M	T	W	T	F	S			List
12				1	2	3	4								1			1	2	3	4	5	6						1	2	3			Date
13	5	6	7	8	9	10	11		2	3	4	5	6	7	8		7	8	9	10	11	12	13		4	5	6	7	8	9	10			2/13/2024
14	12	13	14	15	16	17	18		9	10	11	12	13	14	15		14	15	16	17	18	19	20		11	12	13	14	15	16	17			2/16/2023
15	19	20	21	22	23	24	25		16	17	18	19	20	21	22		21	22	23	24	25	26	27		18	19	20	21	22	23	24			3/19/2024
16	26	27	28	29	30	31			23	24	25	26	27	28	29		28	29	30	31					25	26	27	28	29	30	31			3/20/2024
17									30																									4/22/2024
18	September '24								October '24								November '24								December '24									4/23/2024
19	S	M	T	W	T	F	S		S	M	T	W	T	F	S		S	M	T	W	T	F	S		S	M	T	W	T	F	S			6/10/2024
20	1	2	3	4	5	6	7				1	2	3	4	5							1	2		1	2	3	4	5	6	7			6/11/2024
21	8	9	10	11	12	13	14		6	7	8	9	10	11	12		3	4	5	6	7	8	9		8	9	10	11	12	13	14			7/22/2024
22	15	16	17	18	19	20	21		13	14	15	16	17	18	19		10	11	12	13	14	15	16		15	16	17	18	19	20	21			11/19/2024
23	22	23	24	25	26	27	28		20	21	22	23	24	25	26		17	18	19	20	21	22	23		22	23	24	25	26	27	28
24	29	30							27	28	29	30	31				24	25	26	27	28	29	30		29	30	31
25
2024 (2)

 

[rollis13]

Have a try with my macro:

Option Explicit
Sub test()
    Dim Cell As Range
    Dim DestRowCount As Long
    Dim DestNewRow As Long
    Application.ScreenUpdating = False
    DestNewRow = 13
    DestRowCount = Cells(Rows.Count, "AH").End(xlUp).Row
    If DestRowCount < 13 Then DestRowCount = 13
    Range("AH" & DestNewRow & ":AH" & DestRowCount).Clear
    For Each Cell In Range("A4:AE24").Cells
        If Cell.Interior.ColorIndex <> xlNone Then
            Cell.Copy Range("AH" & DestNewRow)
            Range("AH" & DestNewRow).NumberFormat = "dd/mm/yyyy" '<- here check your local format for dates
            DestNewRow = DestNewRow + 1
        End If
    Next Cell
    Application.ScreenUpdating = True
End Sub

 

[ashringg]

Amazing! Thank you so much for your help!

 

[rollis13]

Thanks for the positive feedback, glad having been of some help.
By the way, you probably need to mark this thread as 'Solved'.

 

[ashringg]

When I run the Marco, my formula CountColorIf formula messes up. I have to go into the cell to fix it. Do you have any suggestions?

 

[rollis13]

Sorry, can't be of any help, I have no idea which cells have a formula with the CountColorIf function and how you're using it since you haven't, until now, attached a fully functional file; what you attached in post #1 (Code XL2BB) doesn't contain formulas that can be evaluated.
In any case, the macro copies from the calendar only the dates of the cells that have a background color to the list area without interfering with any other detail of your sheet so it can't be the cause of the issue.