Conditional Formatting Formula

[vharp91]

Can someone please help me with a conditional formatting formula that I can add to column V that will reference the cells in column AF. I need conditional formatting that will highlight (neutral, bad and good colors) the cells in column V based on what the values are in column AF. For example, if cell value in AF27 is < 0.05 (less than 5%), then it will highlight cell V27 with the neutral (yellow) color, if the cell value in AF27 is >= 0.05 (greater than or equal to 5%) then it will highlight cell V27 with the bad (red) color, and lastly, if cell value in AF27 is < 0.05 and > -0.05 (less than 5% and greater than -5%), then it will highlight cell V27 with the good (green) color. The formula should move down with the rows, so applying the same conditional formatting to rows 28, 29, 30 and so on. So essentially, the conditional formatting in column V should match the current conditional formatting in column AF.

 

[dreid1011]

Perhaps these:

Book1
	V										AF
27	2.7										-6.0%
28	6.24										10.7%
29	4.71										65.5%
30	8.18										28.9%
31	1.27										-70.6%
32	4.68										-9.1%
33	0										0.0%
34	9.97										7.6%
35	0										0.0%
36	5.26										-4.0%
37	5.91										-6.7%
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
V27:V37	Expression	=AND(AF27<0.05,AF27>-0.05)	text	NO
V27:V37	Expression	=AF27>=0.05	text	NO
V27:V37	Expression	=AF27<0.05	text	NO

 

[awoohaw]

You can use the same rules for both column V and column AF by putting $ in front of column number in the conditional formula:

Book1
	V										AF	AG	AH	AI
1												AF<-5%	-0.05<=x<0.05	AF>=.05
2	2.7										-0.06	TRUE	FALSE	FALSE
3	6.24										0.107	FALSE	FALSE	TRUE
4	4.71										0.655	FALSE	FALSE	TRUE
5	5.26										-0.04	FALSE	TRUE	FALSE
Sheet5

Cell Formulas
Range	Formula
AG2:AG5	AG2	=AF2<-0.05
AH2:AH5	AH2	=AND(AF2>=-0.05,AF2<0.05)
AI2:AI5	AI2	=AF2>=0.05

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AF2:AF5	Expression	=$AF2>=0.05	text	NO
AF2:AF5	Expression	=AND($AF2>=-0.05,$AF2<0.05)	text	NO
AF2:AF5	Expression	=$AF2<-0.05	text	NO
V2:V5	Expression	=$AF2>=0.05	text	NO
V2:V5	Expression	=AND($AF2>=-0.05,$AF2<0.05)	text	NO
V2:V5	Expression	=$AF2<-0.05	text	NO