excel conditional formatting multiple functions

问题:

I have a dynamic table with for example 5 columns:

• 1st column -> only has "AA" or "BB" strings.
• 2nd/5th column -> only has one letter values.

I already have rules that:

• If a line has AA in the 1st column, it fills the line with color yellow.
• If a line has BB in the 1st column, it fills the line with color blue.

I use this formula, and it works:

=Search("AA"; $A1)>0

Now I want a rule that:

• If I find the letter M in the other columns, but it's on the AA line, the cell with M it's filled with other color, let's say green.
• If I find the letter M in the other columns, but it's on the BB line, the cell with M it's filled with another color, let's say red.

Is this possible?

EDIT: The figure below represents what I want: table

回答1:

Search wont find things easily across cells.

It is better to use another function. I typically use COUNTIF in this case.

Highlight only one cell in your table and use this as the formula in the conditional formatting. This one gives your the highlight on the cell with "M" only:

=AND(SEARCH("AA", $A1)>0, A1="M")

=AND(SEARCH("BB", $A1)>0, A1="M")

For the row use this:

=COUNTIF($A1,"AA")>0

=COUNTIF($A1,"BB")>0

After putting the conditional formatting in only one cell, change the "Applies to" field to reflect the whole range you wish apply it to. This relies on relative references in conditional formatting. This can get very messy very quickly, so I recommend manually editing the "Applies to" field instead of copy/paste formats to the specified areas.

Edit: Updated the picture and formulas to reflect the update to the question's clarity.

回答2:

By other columns, I assume you mean if any other column in your table has the string M

=AND(Search("AA"; $A1) > 0; OR($B1 = "M"; $C1 = "M"; $D1 = "M"; $E1 = "M"))

回答3:

The two set of formulas are to be:

If I find the letter M in the other columns, but it's on the AA line, it's filled with other color, let's say green.

=AND(SEARCH("AA",$A2)>0,OR($B2="M",$E2="M"))

If I find the letter M in the other columns, but it's on the BB line, it's filled with another color, let's say red.

=AND(SEARCH("BB",$A2)>0,OR($B2="M",$E2="M"))