Conditional format based on two criteria

Closed
r71lima
Posts
3
Registration date
Saturday August 3, 2013
Status
Member
Last seen
August 4, 2013
- Aug 4, 2013 at 12:08 AM
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
- Aug 8, 2013 at 11:57 AM
Hello,

Can someone help me please with a conditional format that would highlight an entire Row in 2010 if Column B on my spreadsheet has any one of 9 Letters (A,B,C,D,E,F,G,H,Z) AND column C has the word "TOTAL" in it? I would be so grateful for any help someone could offer. Thank you so much in advance for any assistance.

My Data example:

1 A TOTAL 334535 (This enire row would be Blue)
2 B 206448 847364
3 B TOTAL 837364 (This entire row would be Blue)
4 Z TOTAL 93473649 (This entire row would be yellow)
5 C 203837 364846

Thank you



4 replies

TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Aug 5, 2013 at 10:55 AM
Hi r71lima,

Not sure how row 4 would be yellow, but try the following formula for conditional formatting:
=AND(OR(B1="A",B1="B",B1="C",B1="D",B1="E",B1="F",B1="G",B1="H",B1="Z"),C1="TOTAL")

Drag the formatted cells down, applying format only.

Best regards,
Trowa
0
Sirs:
Thank you so much for responding however this doesn't seem to want to work. I am currently using MS Excel 2010. In order to conditionally format I go to the Home ribbon, click in the Style section; conditional formatting, higlight cells rules, more rules, and then down to "Use a formula to determine which cells to format." There it offers me a cell to enter my formula, after which it asks me which cells to include the formatting to.

The formula you offered doesn't seem to be able to locate the correct content to highlight properly but instead is highlighting random "cells" instead of the correct entire row.

The formula will I seek should cover cells B8:T300, and highlight in blue an entire row when it finds any of the letters A,B,C,D,E,F,G,H in column B, but on if/AND the word "TOTAL" in column C.

Please let me know if there is anything else you can offer and again thank you so much for trying to assist.
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Aug 8, 2013 at 10:43 AM
Hi r71lima,

I have used Excel 2010 not enough times to guide you.

In Excel 2003 you would select the cells, you wish to change format, first. Then select conditional format, formule is:, and type in the formula.

The lady on this site:
https://contexturesblog.com/archives/2012/06/21/excel-2010-conditional-formatting-nightmare/
talks about "Conditional Formatting Manager" where it is clear to which cells the CF is applied and which rule (formula) is used as shown by a screenshot.

Just don't use a different rule for each row, but only for the first row. Then exit CF and drag the formatted cells down. When you stop dragging, a small square should appear with the option to apply format only. (Hopefully that is still the same between 2003 and 2010.)

Hopefully it will help you out.

Best regards,
Trowa
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Aug 8, 2013 at 11:57 AM
Hi r71lima,

Then try this:
=AND(OR($B8="A",$B8="B",$B8="C",$B8="D",$B8="E",$B8="F",$B8="G",$B8="H",$B8="Z"),$C8="TOTAL")

Remove the ,$B8="Z" if that is no longer a criteria.

Best regards,
Trowa
0