Shading a cell based on several ranges of numeric data

jhippen

New Member
Joined
Jul 10, 2014
Messages
3
How do you shade a cell based on multiple ranges of data?
e.g. A value of...
1 shades red
2-3 shades orange
4-5 shades yellow
6 shades green
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Hi Jhippen,
Please use conditional formatting--new rules--Format cell that contains

create all the rules indicating the colours to shade

NB: the secret is to create all the rules regarding the range you want

Cheers
 
Upvote 0
Highlight all the cells that you wish to have formatted. Go to conditional formatting and choose the option to use a formula to determine which cells are formatted.I chose cells A1 to A10 and entered the formula you see.Excel copies the formula internally so I only have to enterthe formula once, but you do have to make a new rule for each color.

Excel Workbook
A
11
22
33
44
55
66
77
88
99
1010
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A11. / Formula is =A1 = 1Abc
A12. / Formula is =AND(A1>=2,A1<=3)Abc
A13. / Formula is =AND(A1>=4,A1<=5)Abc
A14. / Formula is =A1=6Abc
A21. / Formula is =A1 = 1Abc
A22. / Formula is =AND(A1>=2,A1<=3)Abc
A23. / Formula is =AND(A1>=4,A1<=5)Abc
A24. / Formula is =A1=6Abc
A31. / Formula is =A1 = 1Abc
A32. / Formula is =AND(A1>=2,A1<=3)Abc
A33. / Formula is =AND(A1>=4,A1<=5)Abc
A34. / Formula is =A1=6Abc
A41. / Formula is =A1 = 1Abc
A42. / Formula is =AND(A1>=2,A1<=3)Abc
A43. / Formula is =AND(A1>=4,A1<=5)Abc
A44. / Formula is =A1=6Abc
A51. / Formula is =A1 = 1Abc
A52. / Formula is =AND(A1>=2,A1<=3)Abc
A53. / Formula is =AND(A1>=4,A1<=5)Abc
A54. / Formula is =A1=6Abc
A61. / Formula is =A1 = 1Abc
A62. / Formula is =AND(A1>=2,A1<=3)Abc
A63. / Formula is =AND(A1>=4,A1<=5)Abc
A64. / Formula is =A1=6Abc
A71. / Formula is =A1 = 1Abc
A72. / Formula is =AND(A1>=2,A1<=3)Abc
A73. / Formula is =AND(A1>=4,A1<=5)Abc
A74. / Formula is =A1=6Abc
A81. / Formula is =A1 = 1Abc
A82. / Formula is =AND(A1>=2,A1<=3)Abc
A83. / Formula is =AND(A1>=4,A1<=5)Abc
A84. / Formula is =A1=6Abc
A91. / Formula is =A1 = 1Abc
A92. / Formula is =AND(A1>=2,A1<=3)Abc
A93. / Formula is =AND(A1>=4,A1<=5)Abc
A94. / Formula is =A1=6Abc
A101. / Formula is =A1 = 1Abc
A102. / Formula is =AND(A1>=2,A1<=3)Abc
A103. / Formula is =AND(A1>=4,A1<=5)Abc
A104. / Formula is =A1=6Abc








<colgroup><col width="689"></colgroup>
 
Upvote 0
Hi Jhippen,
Please use conditional formatting--new rules--Format cell that contains

create all the rules indicating the colours to shade

NB: the secret is to create all the rules regarding the range you want

Cheers

Perfect! I was expecting to have to enter all of conditions in the same Rule and couldn't see how.
 
Upvote 0
Highlight all the cells that you wish to have formatted. Go to conditional formatting and choose the option to use a formula to determine which cells are formatted.I chose cells A1 to A10 and entered the formula you see.Excel copies the formula internally so I only have to enterthe formula once, but you do have to make a new rule for each color.

Sheet2

A
11
22
33
44
55
66
77
88
99
1010

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Conditional formatting
CellNr.: / ConditionFormat
A11. / Formula is =A1 = 1Abc
A12. / Formula is =AND(A1>=2,A1<=3)Abc
A13. / Formula is =AND(A1>=4,A1<=5)Abc
A14. / Formula is =A1=6Abc
A21. / Formula is =A1 = 1Abc
A22. / Formula is =AND(A1>=2,A1<=3)Abc
A23. / Formula is =AND(A1>=4,A1<=5)Abc
A24. / Formula is =A1=6Abc
A31. / Formula is =A1 = 1Abc
A32. / Formula is =AND(A1>=2,A1<=3)Abc
A33. / Formula is =AND(A1>=4,A1<=5)Abc
A34. / Formula is =A1=6Abc
A41. / Formula is =A1 = 1Abc
A42. / Formula is =AND(A1>=2,A1<=3)Abc
A43. / Formula is =AND(A1>=4,A1<=5)Abc
A44. / Formula is =A1=6Abc
A51. / Formula is =A1 = 1Abc
A52. / Formula is =AND(A1>=2,A1<=3)Abc
A53. / Formula is =AND(A1>=4,A1<=5)Abc
A54. / Formula is =A1=6Abc
A61. / Formula is =A1 = 1Abc
A62. / Formula is =AND(A1>=2,A1<=3)Abc
A63. / Formula is =AND(A1>=4,A1<=5)Abc
A64. / Formula is =A1=6Abc
A71. / Formula is =A1 = 1Abc
A72. / Formula is =AND(A1>=2,A1<=3)Abc
A73. / Formula is =AND(A1>=4,A1<=5)Abc
A74. / Formula is =A1=6Abc
A81. / Formula is =A1 = 1Abc
A82. / Formula is =AND(A1>=2,A1<=3)Abc
A83. / Formula is =AND(A1>=4,A1<=5)Abc
A84. / Formula is =A1=6Abc
A91. / Formula is =A1 = 1Abc
A92. / Formula is =AND(A1>=2,A1<=3)Abc
A93. / Formula is =AND(A1>=4,A1<=5)Abc
A94. / Formula is =A1=6Abc
A101. / Formula is =A1 = 1Abc
A102. / Formula is =AND(A1>=2,A1<=3)Abc
A103. / Formula is =AND(A1>=4,A1<=5)Abc
A104. / Formula is =A1=6Abc

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4






<tbody>
</tbody>

Thank you!
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top