# Using IF with multiple sets of conditional formatting rules?

#### newmank

##### New Member
I have a spreadsheet of data that I would like to use conditional formatting with...essentially colour coding the cell based upon a range of data. That is easy. However...I have two sets of condition rules that I would like excel to choose from based upon data in another cell.

For example...we have data from the Fall and from the Spring. We have a set of Fall Conditions and a set of Spring Conditions. I want to have excel check Column C for either 'Fall' or 'Spring' and apply the set of conditions that are applicable. (Each set contains for ranges and assigned colour coding for the cells).

Doable?

Thank you.

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### newmank

##### New Member
Thinking this through a bit more...It may be simpler to create the if statement so that if 'Fall' is present it does one set of conditional formatting and if it isn't present it does another.

#### Snakehips

##### Well-known Member
newmank,

If the cells are to be formatted same colour for Fall and Spring then you could use like
=IF(\$C1="Fall",\$V\$1="x",\$W\$1="x")
where e.g. \$V\$1="x" is the test for Fall and \$W\$1="x" is the test for not Fall i.e. Spring.

Otherwise set up two rules with your current conditions as an element of an AND() along with the test for season eg

eg. =AND(\$C1 ="Fall", \$V1 = "x")

Hope that helps.

#### newmank

##### New Member
It begins to...the catch is...there would be multiple possible entries.

So in Column C we would define Spring or Fall. Then in Columns D to W we would enter a numeric value.

I would want the If statement to check for Spring or Fall...then apply a colour based upon a range...for example...0 to 9 is red, 10-14 is Yellow, 15-19 is green and 20-24 is Blue. But for the Spring the ranges change. 0 to 11 is red, 12-16 is yellow, 17-22 is Green, and 20-23 is blue.

I have already applied conditional formatting...but want to change the conditions of the formatting depending on what is noted for Spring or Fall.

#### Snakehips

##### Well-known Member
Ok, I'm assuming that you currently have 4 CF rules set for each column, based on score cell values being within one of the four defined ranges for that column???

If so then you will still need four rules per column (unless some columns share common ranges) and will need to apply a Custom CF formula for each.
I think '17-22 is Green and 20-23 is Blue ' above, is a typo ?? so I have assumed 17-22 and 23-24 for Spring in my example below for column D

So, again, assuming score data starts in row 12 then maybe try...
Sheet2

 * C D 12 Spring 20 13 Fall 20 14 Fall 11 15 Spring 11

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

Conditional formatting
 Cell Nr.: / Condition Format D12 1. / Formula is =IF(OR(\$C12="",\$D12=""),FALSE,IF(\$C12="Fall",AND(\$D12>=20,\$D12<25),AND(\$D12>=23,\$D12<25))) Abc D12 2. / Formula is =IF(OR(\$C12="",\$D12=""),FALSE,IF(\$C12="Fall",AND(\$D12>=15,\$D12<20),AND(\$D12>=17,\$D12<23))) Abc D12 3. / Formula is =IF(OR(\$C12="",\$D12=""),FALSE,IF(\$C12="Fall",AND(\$D12>=10,\$D12<15),AND(\$D12>=12,\$D12<17))) Abc D12 4. / Formula is =IF(OR(\$C12="",\$D12=""),FALSE,IF(\$C12="Fall",AND(\$D12>=0,\$D12<10),AND(\$D12>=0,\$D12<12))) Abc

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hope that helps.

Last edited:

#### newmank

##### New Member
Yes...Helped perfectly! Have it working exactly how I need it now!

Thank you!!!

#### newmank

##### New Member
Ok...maybe I just missed a step...or have just made this more difficult or added a step that I didn't include in my initial description.

Each colour has two ranges...one for Fall and one for Spring. I want the conditional formatting to check for 'Spring' or 'Fall' then apply the proper range.

For example...

For Letter Identification...I want it to check for Fall or Spring. If it is fall then 0-9 = Red, 10-14=yellow, 15-19 = Green and 20-24=blue. But...if it is Spring then 0-11 is red, 12-16 is yellow, 17-22 is green and 23-24 is Blue (the image below says 20-24...but should be 23-24). There are about 150 records in each column...and I get the formula would need to be different for each column depending on the different ranges.

I have attached a screenshot to show the spring/fall ranges.

Thank you again.

Kevin

Replies
11
Views
1K
Replies
0
Views
1K
Replies
0
Views
404
Replies
3
Views
932
Replies
20
Views
325

1,191,117
Messages
5,984,741
Members
439,906
Latest member
Sekiro1899

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