Changing color based on set of predetermined values

This is (I think) a very basic problem. I will try to explain to the best of my ability.

I have a database (small) that lists values of a certain heading by row. I have 6 total rows. I would like the first row, ROW(A) to mimic the data value of ROW(E) and then display only a color based on the value of ROW(E). here is my partial solution and an example for what I have done and would like to see in ROW(A):

I have formatted COLUMN(A) to a custom text format ;;; and assigned A2 to the same value as E3 and then drug the box from A2:An where An represents my last value in COLIMN(A). So now my numerical values for A match those for the corresponding E column for every ROW. I have then attempted to format Column A for specific values. For instance

Conditional Formatting/Manage Rules/New Rule/Use a formula to determine which cells to format/

I then enter formulas like this one "=-500 > E2 > -1000" THEN color the cell light magenta
and this one "= 0 <= E2 <= 200" THEN color the cell pastel orange

The problem is that this type of formatting only applies one cell in COLUMN(A) to the corresponding value in COLUMN(E). I would like each ROW(A) to identify with the corresponding ROW(E) and format the background color of COLUMN(A) accordingly. This way when I look at COL(A) I can tell where it lies immediately on the scale. I will have 6-8 pre-determined colors for COL(A). I would use the pre-defined color scale, but I have large positives and negatives that I would like custom colors for and they do not fall on the three color scale that is implemented automatically in Excel. As I said, I can make the values in A transparent and assign corresponding values of A to E, so all I really need now is a generic way to color cells by value since the values are now hidden. I don't mind making 6-8 lines of qualifiers, but I want to be able to assign all qualifiers to each and every cell in A and have the color change based on A's value.

Also, I used this type of formula (=A2>2000) to assign a color to A2, thinking that I might find a way to apply it generically to all of A, but it actually changes the background color for A1 and I have no idea why.

Thank you for any help that you might provide,

Brad

With this little bit of syntax help, I think the problem is solved.

Thank you!

Brad

Or not. Oops.

I would expect this code to shade the cell A2 this hue of blue. Instead, it shades A1. Why? results.jpg

Decidedly solved. Thanks everyone!

Brad