How to fix a conditional formatting issue on MS Excel

Question

I have set up a set of rules to ensure that cells appear a different colour depending on the text (using validation list); however no matter what I do the cells are being formatted the same for the first two text options despite having different formatting rules in place. How do I fix this irritating error?

Answer 1

Hi @CGAus

Could you please provide the conditional formatting formulas and screenshots of relevant data for better analysis?

The text description is too abstract for such Excel issue. Thanks for your understanding.

Currently, I suggest you make sure that the reference to the data in the conditional formatting formulas is correct, absolute or relative. Here is a thread about data reference, you may have a look: 'Copying Conditional formatting with relative cell referencces in the formula desn't work'.

Any updates, welcome to post back.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 2

Hi,

If your conditional formatting rules are not working as expected in Excel, there are a few things you can check to troubleshoot the issue:

1. Make sure the rules are in the correct order: Excel evaluates conditional formatting rules in the order they appear in the list. If two rules conflict, the rule higher up in the list takes priority. To reorder the rules, go to the Home tab, click on Conditional Formatting, and select Manage Rules. From there, you can drag and drop the rules to change their order.
2. Check for conflicts with other formatting: Sometimes, other formatting applied to the cells can override the conditional formatting rules. So, check for conflicting cell formatting, such as font color or cell background color, and remove any that conflict with the conditional formatting rules.
3. Check the range of cells: Make sure that the range of cells you applied the formatting to is correct.
4. Check for corruption: It may be possible that your Excel file may have got corrupted due to some reasons. So try using "Open and Repair" and check if your issue is fixed.

Hopefully, this will fix your issue. :)

Best Regards.