Search code examples
excelconditional-formattinggenetics

Excel conditional formatting based on multiple cells

I am having trouble coming up with a usable conditional formatting formula in Excel to highlight patterns.

Starting Sample Data

Phase   Sample1 Sample2 Sample3 Sample4 Sample5 Sample6 Sample7 Sample8
{0-}    lm  ll  lm  lm  ll  lm  ll  lm
{0-}    lm  ll  lm  lm  ll  lm  ll  lm
{1-}    lm  ll  ll  lm  lm  ll  lm  ll
{0-}    lm  ll  lm  ll  ll  lm  ll  lm
{1-}    lm  ll  ll  lm  lm  ll  lm  ll
{1-}    lm  ll  ll  lm  lm  ll  lm  ll
{0-}    lm  ll  lm  ll  ll  lm  ll  lm
{1-}    lm  ll  ll  lm  lm  ll  lm  ll
{1-}    lm  ll  ll  lm  lm  ll  lm  ll

(tsv format)

Given the sample above, the formatting I want to do, in English is:

"If, for every row in the spreadsheet, the cell in column A="{0-}", highlight any cell in the same row that contains "lm", else, highlight any cell containing "ll" "

The desired outut would look something like this

enter image description here

PS: For some background (that isn't quite relevant for the question here, but is more for anyone in the future who might google search for a similar question): I am evaluating a genetic map from an outcrossing population based on the output of the program JoinMap. These calls are coded based based on the underlying genotype and the calculated phase is given in the first column. I am, in essence, attempting to re-create the JoinMap output in the program itself but in Excel, so it can be edited.

Solution

  • This is simple actually. What follows is based on your table, cells' adresse and so on...

    0 - Create a table one is going to use for a forthcoming VLOOKUP function. Say, in cells A14:B15, write 

          A     B
14    lm    {0-}
15    ll    {1-}

    1 - Select the cells B2:I10
    2 - Click Conditional formatting > New Rule…

    enter image description here

    3 - In the New Formatting Rule window, select Use a formula to determine which cells to format.

    enter image description here

    In the "Enter your formula" box above, copy paste this line of code

    =$A2=VLOOKUP(B2,$A$14:$B$15,2,FALSE)

    Do not forget to "choose the format". And press OK.