Need Help - Conditional format keeps changing the number format when it should be CLEAR

Iron Contributor

Greetings all,


I have a sheet that has several columns of dates and a column for time, in a table. This workbook is for job searching. However, usually after opening the file, for some reason one of my conditional formats changes from the set number format I put. Happens regardless of whether the sheet is locked or unlocked.




I very deliberately want the number format to NOT be set (or set to Clear) so it can apply to a range of various different number formats - changing the backcolor but not the number format or anything else.


Why is it changing to Date (in this case, it has changed to General before too) when it should be permanently set to Clear?



• The range $C$6:$Q$55 is: "Apply Deadline" through "Follow-up Action" (not shown in pictures but shouldn't be relevant)

• All columns (like "Interview Time" in the picture above) are formatted with the proper number format in the cells themselves already. I also verified this - double checked.


I would be very grateful for any solution to make this conditional formatting rule never change (namely Number format). Thank you!

2 Replies



I'll be very interested in what others have to say, although I do notice that you've had nearly 60 views and no responses yet. That doesn't really surprise me, because what your images show is hard to make sense of.

If it's possible, without violating confidentiality or privacy, could you post a copy of the workbook on OneDrive or GoogleDrive, with a link here that grants access. Images alone often are insufficient to work with.....


That said, I have NEVER seen such an extensive list of Conditional Formatting rules, all applied to the same set of cells. And it would appear that you expect them all to be applied--i.e., you've not checked the "Stop if true" box on any, significantly not checked on the two rules that you appear to be concerned about. It might well be the case that one of the subsequent rules--there are so many!--is just overriding the ones you're asking about.

I tried only circling the affected rules/info in the pictures because I have a lot of rules. Also, the affected rules are on top (the ones above it are applied to column B so are N/A here) so I don't think other rules are overwriting it. Esp. since column I is being changed to a date format due to the affected rules (as seen in the picture) when the only other conditional format rules as well as the cells themselves for I are formatted as "h:mm AM/PM" (time). All in all, the affected rules are just simply changing the number format when they should be set to Clear for everything but fill.

Regardless, I'll message you the workbook as I have done before with you, and therefore, do trust you (as always though, please do not distribute and/or sell for profit). If you find the issue, please share here for everyone to see and your "best response" credit.

Regarding the amount of rules. There are a lot rules and I really did try to have them as minimal as possible but there are a lot of things I want to happen.