-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Shading a cell based on several ranges of numeric data
- Thread starter jhippen
- Start date
skywriter
Well-known Member
- Joined
- Feb 15, 2014
- Messages
- 1,642
- Office Version
- 2016
- Platform
- Windows
[TABLE="width: 689"]
<colgroup><col width="689"></colgroup>[TR]
[TD="class: xl65, width: 689"]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.
[/TD]
[/TR]
[/TABLE]
<colgroup><col width="689"></colgroup>[TR]
[TD="class: xl65, width: 689"]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 | |||
| 1 | 1 | ||
| 2 | 2 | ||
| 3 | 3 | ||
| 4 | 4 | ||
| 5 | 5 | ||
| 6 | 6 | ||
| 7 | 7 | ||
| 8 | 8 | ||
| 9 | 9 | ||
| 10 | 10 | ||
Sheet2 | |||
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| A1 | 1. / Formula is =A1 = 1 | Abc | ||
| A1 | 2. / Formula is =AND(A1>=2,A1<=3) | Abc | ||
| A1 | 3. / Formula is =AND(A1>=4,A1<=5) | Abc | ||
| A1 | 4. / Formula is =A1=6 | Abc | ||
| A2 | 1. / Formula is =A1 = 1 | Abc | ||
| A2 | 2. / Formula is =AND(A1>=2,A1<=3) | Abc | ||
| A2 | 3. / Formula is =AND(A1>=4,A1<=5) | Abc | ||
| A2 | 4. / Formula is =A1=6 | Abc | ||
| A3 | 1. / Formula is =A1 = 1 | Abc | ||
| A3 | 2. / Formula is =AND(A1>=2,A1<=3) | Abc | ||
| A3 | 3. / Formula is =AND(A1>=4,A1<=5) | Abc | ||
| A3 | 4. / Formula is =A1=6 | Abc | ||
| A4 | 1. / Formula is =A1 = 1 | Abc | ||
| A4 | 2. / Formula is =AND(A1>=2,A1<=3) | Abc | ||
| A4 | 3. / Formula is =AND(A1>=4,A1<=5) | Abc | ||
| A4 | 4. / Formula is =A1=6 | Abc | ||
| A5 | 1. / Formula is =A1 = 1 | Abc | ||
| A5 | 2. / Formula is =AND(A1>=2,A1<=3) | Abc | ||
| A5 | 3. / Formula is =AND(A1>=4,A1<=5) | Abc | ||
| A5 | 4. / Formula is =A1=6 | Abc | ||
| A6 | 1. / Formula is =A1 = 1 | Abc | ||
| A6 | 2. / Formula is =AND(A1>=2,A1<=3) | Abc | ||
| A6 | 3. / Formula is =AND(A1>=4,A1<=5) | Abc | ||
| A6 | 4. / Formula is =A1=6 | Abc | ||
| A7 | 1. / Formula is =A1 = 1 | Abc | ||
| A7 | 2. / Formula is =AND(A1>=2,A1<=3) | Abc | ||
| A7 | 3. / Formula is =AND(A1>=4,A1<=5) | Abc | ||
| A7 | 4. / Formula is =A1=6 | Abc | ||
| A8 | 1. / Formula is =A1 = 1 | Abc | ||
| A8 | 2. / Formula is =AND(A1>=2,A1<=3) | Abc | ||
| A8 | 3. / Formula is =AND(A1>=4,A1<=5) | Abc | ||
| A8 | 4. / Formula is =A1=6 | Abc | ||
| A9 | 1. / Formula is =A1 = 1 | Abc | ||
| A9 | 2. / Formula is =AND(A1>=2,A1<=3) | Abc | ||
| A9 | 3. / Formula is =AND(A1>=4,A1<=5) | Abc | ||
| A9 | 4. / Formula is =A1=6 | Abc | ||
| A10 | 1. / Formula is =A1 = 1 | Abc | ||
| A10 | 2. / Formula is =AND(A1>=2,A1<=3) | Abc | ||
| A10 | 3. / Formula is =AND(A1>=4,A1<=5) | Abc | ||
| A10 | 4. / Formula is =A1=6 | Abc | ||
[/TD]
[/TR]
[/TABLE]
Upvote
0
Perfect! I was expecting to have to enter all of conditions in the same Rule and couldn't see how.
Upvote
0
Similar threads
- Locked
- Question
