Conditional Formatting For Multiple Columns

[PrintPostage]

So I'm having an issue getting my formula to work properly.

What I'm trying to do:

Highlight the cells that are greater than 67% with criteria of engineer, delivery, M/S or other. These are in two separate columns.

If I use this:

=OR(G1="Engineer";G1="M/S";G1="Delivery";G1="other";G1="Engineer/Delivery"),AND($F1>.67)

I get an error. Not sure what I'm doing wrong.

If I input these separately, I'll have things that aren't 67%+ highlighted, or things that aren't one of the texts highlighted if it equals 67%+. I want things that match both the criterias to be highlighted. So both things are true, 67%+ and one of the texts.

Thanks for any and all help you can provide.

 

[Marcílio_Lobão]

PrintPostage, Good afternoon.

Try to use:

=AND($F1>.67, OR(G1="Engineer", G1="M/S", G1="Delivery", G1="other", G1="Engineer/Delivery"))

Pay attention at your right separator sign of your Excel version.

Is A SEMICOLON ( ; ) or a COMMA ( , ).

I hope it helps.

 

[PrintPostage]

PrintPostage, Good afternoon.

Try to use:

=AND($F1>.67, OR(G1="Engineer", G1="M/S", G1="Delivery", G1="other", G1="Engineer/Delivery"))

Pay attention at your right separator sign of your Excel version.

Is A SEMICOLON ( ; ) or a COMMA ( , ).

I hope it helps.

I tried using that, but only the percentages are being highlighted and not the text column. At least it didn't error out this time. Any suggestions?

 

[Joe4]

Try selecting both columns F and G and apply this formula to it:

[COLOR=#333333]=AND($F1>.67, OR([/COLOR][COLOR=#ff0000]$[/COLOR][COLOR=#333333]G1="Engineer", [/COLOR][COLOR=#FF0000]$[/COLOR][COLOR=#333333]G1="M/S", [/COLOR][COLOR=#FF0000]$[/COLOR][COLOR=#333333]G1="Delivery", [/COLOR][COLOR=#FF0000]$[/COLOR][COLOR=#333333]G1="other", [/COLOR][COLOR=#FF0000]$[/COLOR][COLOR=#333333]G1="Engineer/Delivery"))[/COLOR]

(switching commas to semi-colons if necessary).

 

[PrintPostage]

Try selecting both columns F and G and apply this formula to it:

[COLOR=#333333]=AND($F1>.67, OR([/COLOR][COLOR=#ff0000]$[/COLOR][COLOR=#333333]G1="Engineer", [/COLOR][COLOR=#ff0000]$[/COLOR][COLOR=#333333]G1="M/S", [/COLOR][COLOR=#ff0000]$[/COLOR][COLOR=#333333]G1="Delivery", [/COLOR][COLOR=#ff0000]$[/COLOR][COLOR=#333333]G1="other", [/COLOR][COLOR=#ff0000]$[/COLOR][COLOR=#333333]G1="Engineer/Delivery"))[/COLOR]

(switching commas to semi-colons if necessary).

It's now highlighting both rows, but it's highlighting things that are random and not highlighting all the text/percentage matches. Aye. Thanks for the help!

 

[Joe4]

It's now highlighting both rows, but it's highlighting things that are random and not highlighting all the text/percentage matches. Aye. Thanks for the help!

It sounds to me like maybe your formula is not aligned with the range you are selecting when you apply this formula.
What is the EXACT range are you selecting when you apply this formula?
Are you putting in the formula EXACTLY as we have written it (except for the commas/semi-colons), or are you changing it in ANY way?

 

[PrintPostage]

It sounds to me like maybe your formula is not aligned with the range you are selecting when you apply this formula.
What is the EXACT range are you selecting when you apply this formula?
Are you putting in the formula EXACTLY as we have written it (except for the commas/semi-colons), or are you changing it in ANY way?

aaaand you were right. It was my range. Thanks for pointing that out. This worked perfectly when I changed the range.

 

[Joe4]

You are welcome!

They key thing to remember when applying a CF formula to a multi-cell range is to write the formula as it applies to the very first cell in the range, and let Excel automatically adjust it for the others.
As long as you have used the absolute range references (the dollar signs "$") in the right place, it will do that.
For an explanation on those range references, see this: http://www.cpearson.com/excel/relative.aspx