Conditional Formatting question for multiple columns

[clockhart10]

Hi all, I'm trying to create a conditional formatting formula to satisfy some conditions. I have a sheet full of survey statistics with columns A through O. I want a formula that will highlight a row (or even just the specific cells) when a certain condition is met. For example, I would want a row (or just the cells) to be highlighted when cell E2 is "Yes" and when O2 is "Yes", and when E3 is "Yes" and O3 is "Yes", etc.

Does this make sense, and is it possible?

I'm on Excel 2011 for Mac

[FDibbins]

hard to say without seeing what your data looks like, but this can be done with a formula in CF. not sure if the examples you gave above all have to be met, but is so, you would need something like...

=and(E2="Yes",O2="Yes",E3="Yes",O3="Yes")

if they are individual requirements, then try something like E2="Yes"

use "new rule", "use formula"

if this isnt what you wanted, please upload a sample workbook, showing what you have, what you want, and how you arrived at that

thanks

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[clockhart10]

surveyshared.xlsx

Ok, I think I attached it. Here's a look at the data. I need columns E and N to match, specifically where they both equal Yes

[clockhart10]

Anyone? I think there is an easy solution, I just don't know it!!

[Norie]

The formula you should use is this.

=AND($E2="Yes",$O2="Yes")

To apply it select all the rows of data, eg A2:R119, goto conditional formatting, select Use a formula... enter the formula and then format as required.

By the way, are you sure it's columns E and O?

Column E does have some cells with 'Yes" but column E has none.

Also the data goes from A to R.

[clockhart10]

Sorry, in this case it should be columns E and N. Thus the equation I would assume is:

=AND($E2$E120="Yes",$N2$N120="Yes")

but this comes up as error.

However, the equation suggested in the comment above (=AND($E2="Yes",$N2="Yes")) worked for that row. However, it only formatted column N. What would the equation be to format column E? I would assume =AND($N2="Yes",$E2="Yes"). But again, how would you do the equation to do the entire sheet?

Thanks so much for your help!

[Norie]

What range did you select for the conditional formatting?

You have data in A2:R119, so that's what you should pick.

That formula is incorrect it should be this:

=AND($E2="Yes", $N2="Yes")

Excel will automatically increment the row number.

[clockhart10]

Awesome, got it, thanks so much. I think I was explaining the wrong thing. I ended up using: =AND($E2="Yes", $O2="Yes") and for the range, I used [E2:E119,N2:N119].

This way, only the cells in those rows are formatted, which is what I need because I'll be doing several conditional formatting rules for other columns.

Thank you so much for your help - you totally solved it!!

[FDibbins]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.