Set bar chart colors based on data?

Warning- rank newbie.
I am trying to make a bar chart based on the attached workbook. What I want to do is assign bar colors that align to column A data. So, 1-3 would be red, 4-6 orange, 7-9 yellow and 10 green.
I also want column B data to print on the vertical axis and have numbers 1 -10 as the horizontal axis.
So far, I have no idea how to assign colors to numbers. Currently the only label I get vertically is from B3, and the horizontal labels are 2, 2.2, 2.4, 2.6 Am I trying the impossible, or can someone point me in the right direction?

Hello & Welcome to the Board,

How's this...

The strategy to making the bars the color you want is by making four seperate series'.

If you look at the formula on Sheet1 cell C3, I've entered, if A3 is less than 3 than give me what's in A3 or else, NA().

The benefit with using NA() is due to the fact it won't chart.

After appying all four column's with a formula, you then create the chart off of column B3:F28.

Go to the chart and select it >> on the ribbon >> Layout >> far left under current selection >> select a series from the drop down >> Ctrl + 1 and format the series with a fill color.

Back on Sheet1, the reason you do not see any of the #N/A is because I added a conditonal formatting to change the font to white if the value is #N/A

Hope this helps

Thanks Jeff, your explanation helps a lot. Where did you put the conditional formatting to change the font color?