copying conditional formatting from one column to another with the criteria being a cell in the new column

[Sid Taylor]

Hello,
Appreciate the site and any help in advance. I have a spreadsheet with 49 columns and at the top of each column I have two rows. the first row is the average of the entire column and in the second row I will be selecting results from a store and want that cell to be red or green based on whether or not it is above or below the average listed in the cell above. I have created that rule in the conditional formatting with "using a formula to determine which cells to format" (=c2>c3 ) then C3 is filled with red and the converse is c3 is filled with green if greater than c2. but I have not been able to find a way to copy that formula to other columns. Excel seems to automatically add a $ to the cell location and any copy / paste or format painter use just ends up referring back to the original column. That means I would need to create 98 of these rules to cover each column.

Is there any way to copy that conditional formatting from one column to the next?

 

[gelu]

Hi Sid,

As soon as you have selected a cell in the CF dialog hit F4 until all the $ is gone.

Cond Form.xlsx
	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
1
2
3			1.956447	0.365036	1.455184	0.581366	3.827345	5.786514	4.804378	4.864148	9.092639	3.315947	5.561592	0.511182	2.296875	2.295737	5.733612	9.536826	0.441954	3.890343	4.125486	0.07092	3.130446
4			1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
5
6
7
Sheet1

Cell Formulas
Range	Formula
C3:W3	C3	=RAND()*10

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4:W4	Expression	=C4>C3	text	NO
C4:W4	Expression	=C3>C4	text	NO

 

[Sid Taylor]

Thanks for the reply. Hitting F4 does change remove the $ signs but as soon as it's saved, they revert back to being an absolute reference and I'm not able to copy without the same issue

 

[gelu]

Hm, why not first delete all the conditional formatting rules from those cells and try again the above?
Maybe it gets confused.

Also, please note the following:

 

[Sid Taylor]

Thanks for this. My problem was not realizing I could enter the entire range in the applies to section with the rule section only having the single cell. Thanks again for the help and clarification!