Is there any way that 'filling' can be restricted to values only and not formatting, perhaps through VBA?

[Rnkhch]

Hello,

I know that it is possible to right-click and drag and then select 'fill without formatting', but is there any formatting changes can be disabled for dragging/filling, perhaps through a VBA code?

Thanks for any input!

 

[Rnkhch]

Thanks, just tested it in a new sheet as in the attached picture, and it didn't work? The highlight still carries down upon double-clicking.

Also, anything can be done to address the dragging scenario?

 

[MARK858]

Are you sure that it is normal formatting and not conditional formatting (Edit: having just tested neither are copying down for me)

Book1
	E	F
3	48	48
4	41	41
5	54	54
6	68	68
7	98	98
8	3	3
9	7	7
10	59	59
11	73	73
12	97	97
13	41	41
14	19	19
15	62	62
16	39	39
17	23	23
18	36	36
19	74	74
20	23	23
21	67	67
22	81	81
23	96	96
24	46	46
25	9	9
26	86	86
27	26	26
28	85	85
29	33	33
30	9	9
31	69	69
32	6	6
33	70	70
34	51	51
35	21	21
36	61	61
37	95	95
38	8	8
39	95	95
40	50	50
41	64	64
42	25	25
Sheet1

Cell Formulas
Range	Formula
E3:E42	E3	=F3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E3	Cell Value	>20	text	NO

 

[Rnkhch]

Yeah, just normal formatting. I just highlighted the cells E2 and E8, and I put some random numbers in F column so that I could double click down in column E.

 

[MARK858]

It doesn't behave like that for me when I double click a single cell in the range, it goes from

Book1
	E	F
3	48	48
4		41
5		54
6		68
7		98
8		3
9		7
10		59
11		73
12		97
13		41
14		19
15		62
16		39
17		23
18		36
19		74
20		23
21		67
22		81
23		96
24		46
25		9
26		86
27		26
28		85
29		33
30		9
31		69
32		6
33		70
34		51
35		21
36		61
37		95
38		8
39		95
40		50
41		64
42		25
Sheet1

Cell Formulas
Range	Formula
E3	E3	=F3

to

Book1
	E	F
3	48	48
4	41	41
5	54	54
6	68	68
7	98	98
8	3	3
9	7	7
10	59	59
11	73	73
12	97	97
13	41	41
14	19	19
15	62	62
16	39	39
17	23	23
18	36	36
19	74	74
20	23	23
21	67	67
22	81	81
23	96	96
24	46	46
25	9	9
26	86	86
27	26	26
28	85	85
29	33	33
30	9	9
31	69	69
32	6	6
33	70	70
34	51	51
35	21	21
36	61	61
37	95	95
38	8	8
39	95	95
40	50	50
41	64	64
42	25	25
Sheet1

Cell Formulas
Range	Formula
E3:E42	E3	=F3

 

[Rnkhch]

I see. I'll test this on my work computer on Monday and see if I can get it to work there. Not sure why it's not working on my home computer

 

[MARK858]

If you can't get it to work on Monday upload a copy of the workbook (sanitize any sensitive data first) to a free file hosting site like www.box.com or www.dropbox.com , mark it for sharing and post the link it provides in the thread and I'll have a look at the workbook.

 

[Rnkhch]

Good morning,

So here's a test file I just made (faster than sanitizing one of my actual files). It's essentially the same as the one I made at home, and it's not working again I just highlighted E3 and double-clicked down, but highlight carries over.

I hope you can figure why it's not working

Test1

 

[MARK858]

I'll have a look when I get in from work but just to be clear you are double clicking the actual cell and not the bottom right corner of the cell

 

[Rnkhch]

Ah, I didn't realize I was supposed to double click the actual cell Just checked, and it's working well.

 

[MARK858]

Happy you got it working