Conditional Formatting - Multiple Conditions

sd2173

New Member
Joined
Feb 16, 2011
Messages
31
First I am wondering if it is possible to insert text into a new cell if conditional formatting conditions are met, and second if it is possible to require two conditions be met in order to insert the text in a new cell.

I am trying to have say column C rows display an "X" if the column A = 10 and column B is < 6.

Not knowing how to insert text if conditions are met I have run the following conditions to display a color and pattern if met:

Condition 1:
Formula is: =($A1=10)
*Yellow highlight

Condition 2:
Formula is: =($E1<6)
*Line pattern

The issue I run into, not to mention the color/pattern does me no good, is I get yellow highlights for all "10's" and line patterns for all "<6's". Not both. This tells me it is reading the conditions seperate, but I don't know how to pair them.

Ideally when I figure this out I am planning to run the same conditions for the following:

A=10 and B<6
A=11 and B<12
A=12 and B<18

My thoughts are that if I continue to run different conditional formatting over the new column I want the "X" to be placed in I will remove previous formattings, so if these can be lumped into one formula or VBA macro it would be ideal.

Any suggestions appreciated, thanks!
SD
 

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.
Hi SD,

Enter this formula in column C1 , =IF(AND(A1=10,B1<6,B1<>0),"x","") . This formula will enter "x" in column C1 , if A1 =10 & B1<6 . I have put B1<>0,because if B1 is blank or 0 , the formula takes it as B1<6.

You change this as you need.

I hope this will help you

Regards , Manuel
 
Upvote 0
Thanks a ton Manuel, this works great! Is there a way to extend the formula to add "or" so I can include the conditions of =11 and <12, and =12 and <18?

Otherwise I have to run it 3 times and combine the 3 columns, which I dont know how to combine the columns into one by pasting the data without overwriting the existing x's.

Hope this makes sense, this is what I'm using now, changed for my data locations:

=IF(AND(D2=10,E2<6,D2<>0),"x","")
=IF(AND(D2=11,E2<12,D2<>0),"x","")
=IF(AND(D2=12,E2<18,D2<>0),"x","")

Any insight appreciated, thanks again!
 
Upvote 0
Hi,

Do you wanna combine all the 3 formulas you have given , to be applied into one cell ? If so, this is how it is done . =IF(AND(D2=10,E2<6,D2<>0),"x",IF(AND(D2=11,E2<12,D2<>0),"x",IF(AND(D2=12,E2<18,D2<>0),"x",""))) .

Check this out.

Regards, Manuel
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

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
Back
Top