How to hard code a range of cells to be filled with a background color?

[johnnyL]

I can't believe I that I can't figure this seemingly simple goal of always having a certain range of cells set to a background color. I have tried formatting the range via right clicking the highlighted range and then 'Format cells\fill'. This seems to work, just until I rerun the script, that is when the colored cell ranges change from what I set it up to originally.

Example Goal:

HardCodedRangeFillColorTestV1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Colour	Qty	Ordered
2	Black	14	18	2
3	Blue	3	4	2	Red	14	6		Green	8	3		Blue	1	3		Brown	4	10		Black	2	5
4	Brown	4	10	1	Blue	2	1		Purple	2	7		Red	4	2						Green	6	1
5	Green	19	6	3	Green	5	2		Orange	6	1										Orange	4	7
6	Orange	10	8	2					Red	0	5										Purple	8	3
7	Purple	10	10	2					Yellow	4	9
8	Red	18	13	3					Black	12	13
9	Yellow	4	9	1
Sheet1

Wrong result that occurs after rerunning the same script:

HardCodedRangeFillColorTestV1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Colour	Qty	Ordered
2	Black	14	18	2
3	Blue	3	4	2	Red	14	6		Green	8	3		Blue	1	3		Brown	4	10		Black	2	5
4	Brown	4	10	1	Blue	2	1		Purple	2	7		Red	4	2						Green	6	1
5	Green	19	6	3	Green	5	2		Orange	6	1										Orange	4	7
6	Orange	10	8	2					Red	0	5										Purple	8	3
7	Purple	10	10	2					Yellow	4	9
8	Red	18	13	3					Black	12	13
9	Yellow	4	9	1
Sheet1

How do I keep the initial range of colored cells from changing? The data displayed hasn't changed, so why are the colored cell ranges changing?

 

[johnnyL]

Why not just reset the colour at the end of the code

Range("A:D").Interior.Color = xlNone
Range("A2:D4").Interior.Color = rgbGreen

Indeed that is a better workaround than my loop workaround.

I can't help wonder, why is a workaround needed at all? This is the first time I have ever seen colored cell ranges change on their own.

 

[Fluff]

They are not changing on their own, you are sorting the cells, which is causing the problem.

 

[johnnyL]

They are not changing on their own, you are sorting the cells, which is causing the problem.

Thank you @Fluff.

The solution, that doesn't require a workaround is to use conditional formatting to set the background (fill) color of the cell range. Sorting doesn't 'mess' with the conditional formatting of cells.

Range A2:D4
= AND(ROW() >1,ROW() < 5)

 

[Fluff]

Glad to help & thanks for the feedback.