Excel 2007 : Using 'OR' 'AND' in Conditional Formatting

**raavi_143** · 07-31-2009, 08:30 AM

Hi,
I have a problem with conditional formatting. Let’s say cell Lj has four values A, B, C, D based on these reading I need to do conditional format some part of the worksheet area. If Lj has ‘A’ I have a different kind of formatting, similarly if Lj has B my formatting rule changes. Formatting is to tell pass fail criteria. Please help how to do this. I tried using different rules like format based on pass fail criteria and then check if Lj has the values corresponding to the formatting and stop if true. But this is not working as my formatting rules lies in a small range between k and l. For the above said method I am using two rules and what if all my rules fail then still my values will lie in the range and will have the conditional formatting rule applied. Can I do this by using formula, help me on how to do this using ‘IF, then else’
Any ideas on how to do this is appreciated.
Thank you!

**Palmetto** · 07-31-2009, 08:38 AM

Unless I am misunderstanding your comments, yYou should be able to use four conditional formats with each rule set to use the cell is equal to "X".

See if the attached is what you want.

**raavi_143** · 07-31-2009, 08:53 AM

I think I am not clear with my question, I am sorry. I will explain it.

Let's say this way, based on cell L4, I need to format H8:H35, I8:I35, J8:35, K8:35 rows with conditional formatting. Each row has different formatting rules. So if L4 has 'A' then each of these rows will have a formatting rule, similarly if L4 is 'B' the formatting changes and the formatting is something like Value not in between 9 and 10, if it is not in between then its fail and if it is in between the above values its pass. There is different range for each row based on the value in L4.
Thank you!

**raavi_143** · 07-31-2009, 10:06 AM

Hi,
i got the logic using 'OR' and 'AND', with formula editor I was able to do this.
=OR(AND($L$14="a",ABS(M16)<i),AND($L$14="a",j<ABS(M16)))

I am using this, Based on the value in L14,i formatted M16 for a given range i-j. (i<j) If L14 value is b then my range will be k-l(k<l), and so on i did with four rules. I would appreciate it if you guys got any better way of solving this.

Thank you!