I use conditional formatting to help me during the development of my spreadsheet solution. One example is with drop-down lists. I usually have my list items defined in specific cell ranges, rather than hard-coded into the data validation range box.

For example, I may have cells E1 to E3 contain list1, list2, list3. I will give E1..E3 a range name. (This is important if you want to access the range from other sheets.) This gives me the flexibility of adding new list items as my development proceeds. The problem I have is when I decide that list1 should now be list4.

While the drop-down list shows up with the new item, I still have a bunch of cells pre-populated with list1. What I do is tag some conditional formatting to highlight values when they become obsolete. Simply follow the instructions above but use a formula such as =ISNA(VLOOKUP(A1,ListRange,1,FALSE)).

The VLOOKUP function tries to find the cell value into the cell range used for my drop-down - ListRange. If the VLOOKUP doesn't find it, it raises a #N/A. The ISNA function will then return TRUE, triggering the conditional formatting.

Now all my cells with obsolete values get highlighted, reminding me to do a search & replace.

Conditional formatting is a great tool for the developer, not just the end-user.