Conditional formatting to adapt font color to background color

I have some formulas in my worksheet that turn out NA errors. I would like to make the worksheet look clean so I don't want the errors to display.

In the past, I used an IFERROR function to handle this and to display blank ("") in case of error. This worked totally fine until I added sparklines to the worksheet. Now I really need the NA and not blanks because blanks are handeled as zero by the sparklines. I need the sparklines to show a gap in case of error.

I wanted to turn the font white in case of error so that the NA errors are not visible. My problem is that I have other conditional formattings in the same area that change the background color. Meaning that some of the errors would be displayed white on a blue background.

I could start writing more formulas to the conditional formatting:

1) if both condition 1 and 2 are true => turn font blue and background blue
2) if condition 1 is true and condition 2 is false => turn font white
3) if condition 2 is true and condition 1 is false => turn background blue

This would get even more complicated if I had more conditions. I feel like there must be a better way - like if condition 1 is true, then adapt to the remaining formatting (conditinal formatting included) of the cell. I tried looking in the conditional formatting dialog boxes for something like "make invisible by mimicking the background color" but I haven't found anything.

Any ideas?

After a bit of searching, this might be what your looking for.

http://www.mrexcel.com/forum/excel-q...ext-colot.html

Thank you very much. Unfortunately it seems that my level of Excel knowledge is not high enough to apply this solution to my problem. I appreciate your effort!

Originally Posted by AYData

After a bit of searching, this might be what your looking for.

http://www.mrexcel.com/forum/excel-q...ext-colot.html

Originally Posted by Fotis1991

Perhaps changing the IF statement in your formulas to IF(AND function. So in this case you'll no need more rules. Just modify the existings..

Thank you, I'm not sure if this is what you meant but it worked. However I had to add a third rule because I don't see how to do it with two:

1) =AND(ISNA(C1);OR((WEEKDAY(C$1;2)=6);(WEEKDAY(C$1;2)=7))) => turn both font and fill blue

2) Cell contains an error => turn font white

3) =OR((WEEKDAY(C$1;2)=6);(WEEKDAY(C$1;2)=7)) => turn fill blue