Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Can someone help me please with a conditional format that would highlight an entire row in Excel 2010 if column B on my spreadsheet has any of these Letters (A,B,C,D,E,F,G,H,Z) and column C has the word "TOTAL" in it?

My Data example:

ColA ColB  ColC    ColD
1    A     TOTAL   334535 (This enire row would be Blue, the letter is a District letter) 
2    B     206448  334535 
3    B     TOTAL   334535 (This entire row would be Blue, the letter is a district letter) 
4    Z     TOTAL   334535 (This entire row would be yellow, this letter is a Zone letter) 
5    C     203837  334535 
share|improve this question
up vote 0 down vote accepted

I hope I have interpreted your description correctly. I am assuming that A-H are District letters and Z is a Zone letter. The conditional format formulas are:

Blue:

=AND($C1="TOTAL", OR($B1="A",$B1="B",$B1="C",$B1="D",$B1="E",$B1="F",$B1="G",$B1="H"))

Yellow:

=AND($C1="TOTAL", $B1="Z")

I have applied these formulas to the entire sheet, so any more rows you add will automatically apply any colors if necessary.

screenshot

Here is a download link:

https://www.dropbox.com/s/x94pzdre67h5d6o/ConditionalFormat.xlsx

share|improve this answer
    
YAY!!!! You are fantatic! Thank you so much! – Rebecca Rickey Aug 4 '13 at 22:55

For a case-sensitive match on the zone, district and total codes, you would use the following formulas in your conditional formats:

Blue:    =IF(AND(OR(AND(CODE($B1)>=CODE("A"),CODE($B1)<=CODE("H"))), 
             EXACT($C1,"TOTAL")),1,0)

Yellow:  =IF(AND(CODE($B1)=CODE("Z"),EXACT($C1,"TOTAL")),1,0)   
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .