Conditional Formatting Positive & Negative Number

[Data123]

Hi I am looking for a way to color positive numbers green and negative numbers red, but as the value becomes greater for green or less for red so does the intensity of the color. Also, I would like to copy this format for other columns and those + and - numbers will range greatly between columns (see below). Lastly, is there a way to choose the min and max green and red for the range of color? Thanks!

Column 1
500
200
100
0
-4
-500

Column 2
4
3
2
0
-1
-4

Zero could be colored white.

 

[Data123]

Thanks dreid, for the detailed explanation! I used conditional formatting and for some reason it colored cell one that I named. See attached pic. What is cell one called when you name/title the column? I ask b/c I tried to Google it, but did not know what to call it. Back to the issue of the colored background. I have verified the fill color is white. I have deleted the cell and it goes back to white then colored again after typing the "One Wk % Chg". I have also cleared the cell with the same result. How do I force it to stay with a white background please?

Lastly, if I want to copy the conditional formatting for a column to use the same customized format for other columns is there a way to do this please?

 

[dreid1011]

Thanks dreid, for the detailed explanation! I used conditional formatting and for some reason it colored cell one that I named. See attached pic. What is cell one called when you name/title the column? I ask b/c I tried to Google it, but did not know what to call it. Back to the issue of the colored background. I have verified the fill color is white. I have deleted the cell and it goes back to white then colored again after typing the "One Wk % Chg". I have also cleared the cell with the same result. How do I force it to stay with a white background please?

Lastly, if I want to copy the conditional formatting for a column to use the same customized format for other columns is there a way to do this please?

The first row is called a header row if you have names for your columns. Can you show the exact formulas you entered for the CF rules? A screenshot will suffice. And yes, you can copy CF rules and change the range they apply to relatively easily in the Conditional Formatting Rules Manager.

 

[Data123]

The first row is called a header row if you have names for your columns. Can you show the exact formulas you entered for the CF rules? A screenshot will suffice. And yes, you can copy CF rules and change the range they apply to relatively easily in the Conditional Formatting Rules Manager.
View attachment 107321

I forgot to add the screenshot of the colored background sorry. Here it is.

 

[dreid1011]

I forgot to add the screenshot of the colored background sorry. Here it is.

I need to see the actual rule you are using, not just the results.

 

[Data123]

The first row is called a header row if you have names for your columns. Can you show the exact formulas you entered for the CF rules? A screenshot will suffice. And yes, you can copy CF rules and change the range they apply to relatively easily in the Conditional Formatting Rules Manager.
View attachment 107321

Thank you! I got it. I duplicated the rules as you showed me and then cleared the issue column then replied the rule. May I ask you another question? I would like to sort a column and expand it to all the other columns, but I have a column with holidays (dates) listed. Several of my formulas will exclude those dates purposely, by listing the cell numbers by location. When I try to sort the column and expand the sort it moves the holiday dates all over and scrambling them to many cells. Is there a way hide the holiday dates column away from the spreadsheet, but still exclude them in necessary formulas?

 

[dreid1011]

Thank you! I got it. I duplicated the rules as you showed me and then cleared the issue column then replied the rule. May I ask you another question? I would like to sort a column and expand it to all the other columns, but I have a column with holidays (dates) listed. Several of my formulas will exclude those dates purposely, by listing the cell numbers by location. When I try to sort the column and expand the sort it moves the holiday dates all over and scrambling them to many cells. Is there a way hide the holiday dates column away from the spreadsheet, but still exclude them in necessary formulas?

You're welcome, and glad you got it sorted. Since this is a new and completely unrelated question, you should make a new thread. I believe that is quite doable, but I am not experienced in some of the date formulas to know straight off.

 

[Data123]

Thanks Dried1011, you are awesome!