Data validation formula to change cell colour based on percentage of value in the above cell

[xpat]

I'm trying to create data validation rule that will change cell color depenidng on percentage of the value from the cell above e.g. G7/G6>0.8 change cell color to green, G7/G6>0.5 red etc.

How do I create a formule in data validation that will apply this rule to all selected cells without referencing a specific cells for this calculation?

The formula works if I used a specific cell references but I need something more flexible that will apply the rule across all calls in the same row but consecutive column.

Thank you in advance for the support.

 

[myall_blues]

Conditional formatting formulas work in the same way that formulas do, in that as they move from cell to cell the references to columns and rows update accordingly, unless preceded by a $ symbol.
The key is to highlight the entire range you want formatted but write the formula as it applies to the first cell/range/row/column you want formatted.
So in your case (see example below) and assuming your data of interest begin in row 3:

• Select rows 3 to the bottom row
• Conditional formatting->New rule->Use a formula...
• Write the formula(s) as it applies to the first row.
• Repeat for each condition.

Book1
	A	B	C	D	E	F	G	H	I	J
1							Value	Ratio for example
2							8
3							10	1.3
4							3	0.3
5							6	2.0
6							4	0.7
7							5	1.3
8							8	1.6
9							2	0.3
10
Sheet1

Cell Formulas
Range	Formula
H3:H9	H3	=G3/G2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
3:9	Expression	=$G3/$G2>0.8	text	NO
3:9	Expression	=AND($G3/$G2>0.5,$G3/$G2<=0.8)	text	NO
3:9	Expression	=$G3/$G2<=0.5	text	NO