excel - Conditional Formatting - Color Scale entire row based on one column -

suppose want color scale complete rows on basis of values in column (using excel inbuilt color scale option in conditional formatting menu). how achieve this? please see following image

you don't need vba this, really.

one thing keep in mind here won't able achieve desired behavior single conditional formatting rule; you'll have have separate rule each sales-based row color definition. second thing: have found easier achieve desired conditional formatting behavior in excel using named ranges rules instead of regular formulas.

with these issues in mind, follow these steps create named range , create conditional formatting rules.

1. first, select first sales cell on sheet (uppermost row)
2. next, give cell name, "sales". pressing ctl+f3, or select formulas->name manager ribbon. select new... in name: enter sales , in refers to: enter =$xn x column of first sales cell, , n row number. hit enter.
3. now select entire cell range wish exhibit behavior
4. select home->conditional formatting->new rule...
5. select use formula determine cells formatand enter =sales=number number sales number wish trigger color
6. select format , fill tab. need decide background color want sales number chose. can choose other formatting options, font color, etc.
7. hit ok, ok, ok. repeat steps 3 6 each different sales figure/color combination want. if want color "all sales less x", in rule enter =sales<number (< "less than"; can <=, "less or equal to"). if want rule happen when between 2 numbers, can =and(sales<=ceiling, sales>=floor), ceiling , floor upper , lower bounds. if want color "all sales greater x", can =sales>number.

edit:

to make entering conditional formulas bit easier, can use "stop if true" feature. go home->conditional formatting->manage rules, , in dropdown menu choose this worksheet. see list of rules apply sheet, , there "stop if true" checkbox right of each rule.

for each row color rule, put check in "stop if true" checkbox. formulas can (just example):

• =sales>25 green rule
• =sales>10 yellow rule
• =sales>0 red rule

etc, instead of this:

• =and(sales>0,sales<=10) red rule
• =and(sales>10,sales<=25) yellow rule
• =sales>25 green rule

the stop if true box means once formatting rule has been applied cell, cell not formatted again based on other rules apply it. note means order of rules matter when using stop if true.