Forum Discussion

Kyra Silbernagel's avatar
Kyra Silbernagel
Copper Contributor
Jun 14, 2017

Conditional formatting applied to many cells separately

Hello,

 

 

 

I'm trying to format a group of cells called "bases" (B1:H1) based on if A1 contains a certain letter. For example, if A1 contains an "A", I want B1 to turn yellow. If A1 contains "B" I want C1 to turn yellow as well, and so on. I know how to apply the rule to each cell individually, but I'm wondering if there's a way to apply it to cells B1:H1 all at once. I tried using formulas using sumproduct and isnumber, but I can't get it to work. I'm also actually applying many more options than just B1:H1, there will be about 24 total, so I was looking for a quicker way than putting each option in quotes and separting by a comma.

 

Thank you!

excel
  • Logaraj Sekar's avatar
    Logaraj Sekar
    Steel Contributor
    Jun 15, 2017

    Hi Kyra Silbernagel,

     

    Just Go with this.

    Select Cell Range "B1:H1"

     

    In Conditional Formatting -> New Rule -> Use a formula to determine which cells to format.

     

    Apply the formula

    =SEARCH(B1,$A1,1)>0

    Select the Color you want to apply.

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Jun 15, 2017

    Hello Kyra

     

    Kyra Silbernagel wrote:

     

    I know how to apply the rule to each cell individually, but I'm wondering if there's a way to apply it to cells B1:H1 all at once.

    No, there isn't.

    It's pure logic: For every format you need a separate rule.

     

     

Resources