# Conditional Formatting with changing multiple criteria

#### jhlwilson

##### New Member
I have an annual percentage that I am looking to apply a conditional format based upon the current month. For example, my annual loss percentage goal is 12%, which breaks down to 1% per month.

I have a cell showing my annual percentage year to date. The data ran is through March so if the cell is equal to or less than 3%, I would like it to color green. If the cell is greater than 3% I would like it to color red.

However, the "3%" will not be static as it will change when I import new data to the report. When April is done, I will then have Jan-April data and the percentage will move to 4%.

What I have done so far is created a drop down data validation cell. In this drop down it displays each month (1/1/2013, 2/1/2013, 3/1/2013, etc...) When I import new data I would select the corresponding month that this data is ran through.

I also created a table using two columns. The first column reflects the drop down box with months. The second column displays the percentage for each month.

Example...
O P
1 Date Loss %
2 1/1/2013 1
3 2/1/2013 2
4 3/1/2013 3
5 4/1/2013 4

I may be way off on my methods of accomplishing this format but wanted to display my efforts so far. I believe at this point I am stuck on what type of formula I would use to match both dates and then compare column P with my year to date percentage that is pulled from my data.

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### etaf

##### Well-known Member
is all the data filling columns
so could you counta and then use the value

=IFERROR(LOOKUP(COUNTA(A2:E2),{0,1,2,3,4},{0,0.01,0.02,0.03,0.04}),"invalid")
so we are counting row 2 for any data thats in column A,b,c,d or e
and then depending on the count - we are using the 2nd array to return the value
put that in a cell somewhere-so you can then compare that cell with your current percentage to use as a conditional format
Arrays cannot be used in the conditional format formula

Replies
1
Views
133
Replies
1
Views
1K
Replies
9
Views
330
Replies
12
Views
315
Replies
0
Views
441

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,707
Messages
5,833,242
Members
430,199
Latest member
Petty queen

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