Conditional formatting multiple rules to single cell

Question

I have used one formula to change the font colour if a cell contains the text "complete" & another formula to add a light blue fill to the entire row if the word "odd" appears in a cell which is in a different column to the first formula.

The conditions seem to clash & although I have changed and played with the order of the conditions & checked there is no conflict in the fill/background or the font colour, even when changing the font colour manually again the fill from the second formula returns the font the cell to black although it is set to automatic in the conditional formatting rule with the fill ?

I wonder where i am going wrong as everything suggests the two conditions should work fine together though when the fill is applied the font goes back to black when it should be green ?

Have you any suggestions ?

Answer 1

I found the issue, I needed to press clear on the font section of the formatting on the fill condition and then clear on the fill. Basically as a rule I press clear on anything not required in that condition and this seems to avoid the conflict and run upto 6 rules on the same cell with no problems!

Answer 2

From my experience with conditional formatting, I believe that it applies a format to the entire cell rather than individually modifying fill or font colour, so formats will not combine. I had a similar situation and solved it by creating an additional rule. In your case, one that applies your desired font colour and background fill to the 'complete' cell if the same row also contains 'odd' using the AND() function.

Answer 3

You can combine multiple conditional formats overlapping cells/rows. (Excel's just a little picky that you do it just right!)

• Always use New Rule as opposed to using the built-in rules.

• Choose Use a formula to determine which cells to format.

• For your example question, I populated the cells as shown above. Select cell B4 and set the conditional formatting as above =($B4="complete") . Note that I removed a $ (dynamic vs absolute cell reference) that was automatically filled in, so that I can fill the formatting's formula down.

• Highlight entire Row 4 and set the conditional formatting as below. Again, note the modified $.

  

• When it's working properly, copy entire Row 4, select the rows to which the formatting should be applied, and Paste Formatting.

[

Obviously the steps will vary for you depending on your data and what's in the neighboring cells whose formats you may or may not want affected by the formatting and copy/pasting.

Let me know if that works for you.