excel 2013. conditional formatting for color fill based on an assigned value

[nicolehill]

I am making a table comparing what should be done with what is being done, while also prioritizing the "should be done" tasks according to most important to least important.  Column A contains information that describes what should be being done. The organization descending for column A is based on Sections, not relative to priority. For example, let's say it is on chores or household duties. So Row 1 says Yard/Garage, Row 2 says wash car, row 3 says mow lawn, and row 4 says Trash cans to Curb. Row 5 may be another section such as Kitchen, Row 6 wash dishes, row 7 clean stove, etc. 

Each Row of column A does have an assigned priority value (8-1, with 8 as highest priority), however, I am not sure how to "imbed" it or assign it.  So, say washing the car in row 2 is a 6 value,  mowing the lawn is of the highest priority with a value of 8, as is washing the dishes. Maybe Cleaning the stove in row 7 has a priority of 1.

What I really want to do is color Column A with each of column A's row's color designation based on the priority value.  I would like an assigned 8 value that appears in column A to fill with a dark green, 7 light green, 6 a lighter green, and 5 a tinted lightest green. Then 4 could be a darker orange, 3 lighter, and so forth.  So when I look at the completed table, for example, Column A R1 would be white (normal) because it does not have an assigned value as a section. Then row 2 would be a lighter green, Row 3 would be dark green because its assigned value is 8. Row 7, due to its priority of 1 would be the tinted lightest orange.

How can I do this aside from just going into each box individually and filling manually?

Thank you, and sorry it is confusing..

[DaveLembke]

The logic as to what has greater priority than other tasks will probably be the hardest part for this. You would need to create the logic of true and false as to tasks completed or yet to be completed still. Many people create a flow chart to make it easier to write and look back at as they are writing it. Basically each task has a priority weight, and based on the weight of others, others may be a greater priority than it itself, however if those are completed then the specific task may be the greater priority with others completed.

I created a similar setup for a scheduling spreadsheet years ago so that while I was on vacation another supervisor knew based on the output of the spreadsheet and rules I set with weights, that one employee should be given a specific task over others, this way the most qualified dealt with there area of expertise and those with lesser qualifications were indicated on this spreadsheet to be given duties more geared towards their expertise 'easier tasks' since they were not fully trained on all systems yet for example.

Here is a link that shows rules that can be set: https://www.ablebits.com/office-addins-blog/2013/10/18/change-background-color-excel-based-on-cell-value/

I ended up making an area of logic at the bottom of my spreadsheet that the output was controlled by the input data cells at the top of the spreadsheet. * This is logic that is best completed when your very alert not something to try to create on lack of sleep or running low on coffee. Also you should test it after to make sure that it does exactly what you want it to. I tested mine and found that there was a flaw due to a typo. I fixed the typo which was buried in IF-THEN-ELSE logic and was all set. Mine was probably more complex than yours because I needed logic for 7 different days of the week in which weekly routines were needed to be completed offset by 1 day of other machines etc so that the weekly tasks didnt all hit on the same day.

You will also need to learn in addition to formatting rules also how to perform IF / THEN logic within excel such as the following:

http://www.techonthenet.com/excel/formulas/if_nested.php