I would like to colour cells which contain the word from column A but only those which are in the same row. There are over 10000 rows, so it should be easily adaptable to other rows.Example:1st dog: -> dog walker -> cat2nd cat: -> dog -> hamster3rd hamster: -> hamster food -> hamster cageAll cells in line 1 which contain dog should be coloured and so on. Can someone help me?

Bennadeau As a cosmetic comment, simple =SEARCH($A1,B1) If it returns an error format trigger won't work

Forum Discussion

Copper Contributor

Sep 08, 2020

Solved

Conditional formatting row by row (text contains)

I would like to colour cells which contain the word from column A but only those which are in the same row. There are over 10000 rows, so it should be easily adaptable to other rows.

Example:

1st dog: -> dog walker -> cat
2nd cat: -> dog -> hamster
3rd hamster: -> hamster food -> hamster cage

All cells in line 1 which contain dog should be coloured and so on. Can someone help me?

excel

SergeiBaklan
Sep 08, 2020
Bennadeau

As a cosmetic comment, simple

=SEARCH($A1,B1)

If it returns an error format trigger won't work

5 Replies

Bennadeau
Iron Contributor
Sep 08, 2020
Hi Muemin,
Try this in your conditional formatting. =IF(SEARCH($A1,B1)>0,TRUE,FALSE)
And apply it to all your cells.
- SergeiBaklan
  Diamond Contributor
  Sep 08, 2020
  Bennadeau
  
  As a cosmetic comment, simple
  
  =SEARCH($A1,B1)
  
  If it returns an error format trigger won't work
  - Muemi
    Copper Contributor
    Sep 09, 2020
    I t works perfectly. Thank you Bennadeau & @SergeiBaklan
SergeiBaklan
Diamond Contributor
Sep 08, 2020
Muemi

You may apply rule with the formula like

=ISNUMBER(SEARCH("dog",A1))

to your entire range (1000 row) if the range starts from A1