View original (French)

Conditional formatting + merged cell

Solved
anne26 Posted messages 35 Status Member -  
anne26 Posted messages 35 Status Member -
Hello,
I would like to know if it is possible to apply conditional formatting with merged cells. Let me explain:

I would like the data in column "C" to be displayed in red when the word "house" appears in column "A". The problem is that this is a pivot table and the word "house" is written only once for all corresponding rows (either in the first row or for the whole range but in a merged cell).
When I write my formula for conditional formatting, Excel does not understand that the word "house" corresponds to multiple cells in column "C"; for it, it only corresponds to the cell in the first row. 
 A B C 1 1 House …. xxx 2 …. zzz 3 …. aaa 4 …. bbb 5 5 Car …. uuu 6 …. ddd 7 …. ttt


If I enter my formula in "Conditional Formatting/Formula is": " =IF(SEARCH("House",A1),TRUE)", Excel only highlights "XXX" in red because for it, only A1 and A5 exist (as A1, A2, A3, A4 are merged).
Aside from writing "house" on every row, is there another solution???
Thank you in advance for your help. 
Configuration: Windows XP Internet Explorer 6.0

4 answers

  1.
    wilfried_42 Posted messages 912 Status Contributor 245
     
    Hello

    there's just one method: create an additional column that you will hide later (let's say column C)

    in C1 put: =A1
    in C2 put: =IF(A2="";C1;A2)
    drag the formula down

    then base your conditional formatting on the new column

    I confirm that merging cells, although supported by Excel, does indeed cause problems for array-type formulas and in VBA; personally, I use them very rarely and only for titles (out of laziness)

    best regards
    2
  2. anne26 Posted messages 35 Status Member 1
     
    Thank you all, what great ideas that are going to make things easier for me!! Thank you.
    0
  3. qwerty- Posted messages 17655 Registration date   Status Contributor Last intervention   1 451
     
    a merged cell CAN NEVER BE RECOGNIZED AS MULTIPLE CELLS.

    that's the principle of merging.

    otherwise, it would be useless.

    advice: use merging as rarely as possible for cells that are part of a formula.
    -1
  4. qwerty- Posted messages 17655 Registration date   Status Contributor Last intervention   1 451
     
    In your case, you can write "house" in each cell, then make the color of the "houses" you don't want to see white.

    That way it wouldn't look like you wrote "house" all the time.
    -1