conflict in conditional formatting

[blueyes]

Hi, I hope someone can assist with my predicament!

I have an Excel spreadsheet (2010) which has two tabs, one a list of dates and tasks, the 2nd sheet is a calendar. When a date is entered into the list, depending on the type of task I have got it to conditionally format the appropriate date cell on the calendar tab using the match formulae. So far so good.

I now have an issue that I have different task types occuring on the same date. I have ensured there are no conflicting conditional formats, for example a 'data deadline' date fills the cell blue, and a 'report produced' date turns the font red.

However, Excel must think there is a conflict because it only takes the first rule and ignores the second (as per the precedence order in the conditional format manager). It'll fill the cell blue but will not turn the font red. If I change the order of the rules then it'll do the opposite, but never both.

I thought this might be something to do with the font colour in the 'data deadline' rule (which fills the cell blue), but this is set to 'automatic', which sounds right. I'm not sure what else I could set this to if this is whats causing the problem?

If anyone could help and advise on what the conflict could be here, that'd be great! All I want is for the cell to fill with the colour and the font to change colour if 2 task types are shown on the same date.

Thanks very much

Lindsey

 

[baitmaster]

welcome to the board

do you have the "stop if true" feature ticked on either condition? Untick it if you do

 

[blueyes]

Thank you, no I don't have the stop if true box ticked on any of the rules

 

[baitmaster]

then it does sound like there is a conflict in your formats

To apply to separate CFs, one colouring the font, the other colouring the background, you need
1) font colour = your choice, background = no colour
2) background = your choice, font colour = automatic

Something I spotted which I never noticed before, if you actually select "font colour = automatic", it doesn't seem to work. If you start from nothing and apply the background colour, but leave font colour alone (by default it still says automatic, bizarrely), then it seems to work. I'd suggest you cancel that format and start it again from scratch. I also managed to reset it using the "clear" button on the CF form, after messing around with the font colour

 

[blueyes]

Lovely, thank you. You were right, only by deleting the CF rules I'd written and re-doing them from scratch worked, how strange! I couldn't get the clear button to function as it should either. Thanks for your help.

 

[baitmaster]

MS Excel - does exactly what you want, except on those occasions when it decides to confuse you with something really annoyingly obscure...

 

[blueyes]

Very true!!