Conditional formatting + merged cell
Solved
anne26
Posted messages
35
Status
Membre
-
anne26 Posted messages 35 Status Membre -
anne26 Posted messages 35 Status Membre -
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.
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.
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 réponses
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
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
