Excel 2010 compare two columns with multiple conditions

Hi Experts,

I would like to compare the two columns and highlighed the row if the specific condition is met like below:

Comparison 1:

- compare the strings in column A and B whether strings in the columns are capitalized or not.  E.g.,

Column A           Column B
SMITH           smith

Note: I would like to compare purely the strings if it's capitalized or not. I'm not comparing if the strings matches or not.

Comparison 2:
- compare column A and B and if column A contains specific strings and column B has specific set of strings, highlight the row.

E.g.,
If the column A contains the string, "Open" and column B contains either "In Queue" or "In Test", or "In Progress", then, highlight the row

Thanks!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
I am confused about 1st condition.

Do want to highlight only if one is capitalized and the other isn't, or do you want to highlight as long as there is capitilization in either or both columns?
0
Author Commented:
I want to highlight only if one is capitalized and the other isn't.

Thanks!
0
Commented:
Sorry, this got lost in my inbox....  are you still requiring assistance?
0
Author Commented:
Yes. It would be great if someone can show me how to do it.
0
Commented:
Assuming columns A and B, then select those columns:

Go to Home|Conditional Formatting|New Rule, then select:  use a formula to determine which cells to format

Then enter formula:

=AND(\$A1<>"",\$B1<>"",OR(AND(EXACT(\$A1,UPPER(\$A1)),EXACT(\$B1,LOWER(\$B1))),AND(EXACT(\$A1,LOWER(\$A1)),EXACT(\$B1,UPPER(\$B1)))))

where A1 and B1 are top most cells.

click Format and choose colour.

click ok, then click New rule and repeat as above with formula:

=AND(\$A1="Open",OR(\$B1="In Queue",\$B1="In Test",\$B1="Progress"))

click Format and choose colour.

Click Ok.

Now in the rules list, make sure that last rule is at the top, and check the box that says Stop if True.

Let me know how it works.... and sorry again for losing track of your thread.
0

Experts Exchange Solution brought to you by