# Conditional formatting question

#### BigBonna

##### New Member
Hello,

I have a table similar to the one below. The bold numbers are the maximum scores for a column.

 Person 5 4 6 4 3 6 A 4 6 2 5 2 2 B 1 5 5 4 1 1 C 3 1 6 2 6 3 D 6 3 6 5 2 1

<tbody>
</tbody>

I would like to create a conditional formatting rule that would colour the cells in column B:

• red if the value is < b1/3
• yellow if the value is >= b1/3 but <= 2*b1/3
• green if the value is < 2*b1/3
• blue if the value is > b1 (to signify an error)

I used B as an example, but I would like to do this for all columns.

Is it possible to create this in one rule? If so could someone please link me to an appropriate learning resource? I am having no luck searching.

Thank you.

<colgroup><col style="width:48pt" span="7" width="64"> </colgroup><tbody>
</tbody>

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Select B2:G5 and apply the following Conditional Formatting formulas:

=B2<(B\$1/3)
=AND(B2>=(B\$1/3),B2<=(B\$1*2/3))
=B2>(B\$1*2/3)
=B2>B\$1

That's wonderful. Thank you very much

Would you care to explain briefly how that works? Conditional formatting is something I'm yet to explore to its full potential and I'm eager to learn.

Edit: It turns out my "Person" cell (A1) is actually E4 on my spreadsheet. Naturally the formulae won't work, even when I try changing them to the new ranges/cells and replacing B with F. Any advice?

Last edited:
It just evaluates the formulas as if they were in a cell and applies the format of the condition that returns TRUE. Here's a tutorial:

http://www.contextures.com/xlCondFormat01.html
Ignore my edit from before, I was looking at B\$1/3 as B * 1/3 for some bizarre reason - too much work, too little sleep!

One more question, how would I go about setting it so there is no format if the cell is blank?

Never mind, problem solved. I was doing the right thing (=b2=""), it was just below the other formats for some reason. Thanks for all of the help, Andrew.

Replies
1
Views
401
Replies
4
Views
105
Replies
41
Views
680
Replies
7
Views
603
Replies
6
Views
356

1,196,269
Messages
6,014,352
Members
441,816
Latest member
Klingon1960

### 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.

### Which adblocker are you using?

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

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