Multiple Conditional Formating

amatthews

New Member
Joined
Sep 20, 2006
Messages
15
I am wanting to know how I can keep my previous to conditioning formats in excell while adding another without it deleting my previous two. Let me explain. I have two conditional formatings. For your reference if it helps, Condition 1 says: Formula Is... =AND(C3-TODAY()<=0) this is set to change to red. Condition 2 says: Formula Is... =AND(C3-TODAY()<=7) this is set to change to blue.

My question is... I want to set up another condition formula and basically I want this formula to be a "Strikethrough" without this formula deleting my other two formulas mentioned previously. When my balance reaches zero I want excel to automatically put a Strikethrough to help me keep track of what is closed out and what is still open. Someone was already kind enought to provide me the correct formula to do the Strikethrough when my balance reaches zero but my next problme is how to combine multiple conditions without erasing what I have already entered in. Any help on this would be greatly appreciated!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

riaz

Well-known Member
Joined
Jun 27, 2006
Messages
779
You are allowed three conditional formatting conditions, so just add your third condition to say =AND(C3-TODAY()=0)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,439
Office Version
  1. 365
Platform
  1. Windows
amatthews

I can't see why these conditions have AND in them. AND is used when you have 2 or more conditions you want to be met but there is only 1 condition in each formula. So your existing formulas could be simplified to:
=(C3-TODAY())<=0 - red
=(C3-TODAY())<=7 - blue

Further, the TODAY() function is 'volatile' and recalculates regularly. This can slow the performance of your sheet considerably. A better solution is to put =TODAY() in a single unused (and possibly hidden) cell and refer to that cell in your Conditional Formatting formulas.

Now to your specific problem, which I am not sure that I have interpreted correctly (I am not sure what 'when my balance reaches zero' means).

1. Put =TODAY() in an empty cell, say Z1
2. Condition 1 Formula is: =(C3-$Z$1)=0 - strikethrough
3. Condition 2 Formula is: =(C3-$Z$1)<0 - red
4. Condition 3 Formula is: =(C3-$Z$1)<=7 - blue

Post back with more details if this is not what you want.
 

amatthews

New Member
Joined
Sep 20, 2006
Messages
15
Hello Peter! Thanks for your help on this. I hope this message finds you. Let me clarify... I have a column in B that lists all my expected due dates. In each of the cells in the column I have the following conditional formatting:

Condition 1. Formula Is. =AND(B3-TODAY()<0) This is set to RED
Condition 2. Formula Is. =AND(B3-TODAY()<7) This is set to BLUE

I think I can see what you are trying to tell me but I don't think I made myself too clear on how I want to use the Strikethrough. I have another column, (column M) which shows my balance, which is a quantity. I want a strikethrough in cell B3 if my balance reaches zero. What I am trying to do is create another Condition to show the following:

Condition 3. Formula Is. =AND(M3=0) This would be set to Strikethrough

When I try and do this it does not work. It seems I don't know how to setup multiple conditions so that all three can work.

BTW... I like your suggestion on the today's date formula... no wonder my spreadsheet was slow!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,439
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

amatthews

If M3 is 0 and also (B3-TODAY()) <0 then do you want B3 to be:
a) just strikethrough
b) just red, or
c) strikethrough and red?
 

amatthews

New Member
Joined
Sep 20, 2006
Messages
15
Sorry Peter for not getting back to you sooner. I would like M3 to be:

a) Just Strikethrough

I say this because I already have the other conditioning formats set up. The only thing I need left to do is to setup the strikethrough.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,439
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Sorry Peter for not getting back to you sooner. I would like M3 to be:

a) Just Strikethrough

I say this because I already have the other conditioning formats set up. The only thing I need left to do is to setup the strikethrough.
OK, but I am not sure that you are fully understanding how Conditional Formatting works. In particular, are you aware that Excel checks the conditions in order and as soon as it finds a condition that is true it applies that format and no other? That being the case, you need to decide whether
M3 = 0 (strikethrough) over-rides, say, (C3 -$Z$1)<0

So your solution may be:
1. Put =TODAY() in an empty cell, say Z1
2. Condition 1 Formula is: =M3=0 - strikethrough
3. Condition 2 Formula is: =(C3-$Z$1)<0 - red
4. Condition 3 Formula is: =(C3-$Z$1)<=7 - blue

or perhaps the conditions may need to be:
Condition 1 Formula is: =(C3-$Z$1)<0 - red
Condition 2 Formula is: =(C3-$Z$1)<=7 - blue
Condition 3 Formula is: =M3=0 - strikethrough
 

amatthews

New Member
Joined
Sep 20, 2006
Messages
15
Got it! Thanks Peter!! I tried both ways out and the first one you suggested work our perfectly. This is exactly what I was needing. Thanks again for your time in helping me with this.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,439
Office Version
  1. 365
Platform
  1. Windows
Got it! Thanks Peter!! I tried both ways out and the first one you suggested work our perfectly. This is exactly what I was needing. Thanks again for your time in helping me with this.
No problem - glad we got there in the end. Perseverance is a great asset with Excel as it is in most endeavours :oops: :)
 

Forum statistics

Threads
1,136,612
Messages
5,676,800
Members
419,651
Latest member
alexanderguhr

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
Top