Conditional Formatting is a great feature. Before it came along, dynamic formatting was pretty limited. Yet the old way still exists and can be useful in some situations.

On the Number tab under Format>Cells, there is a Custom category where you can create your own number formats. Take this custom format for example:

[Green]General;[Blue]General;[Red]General;[Yellow]General

This says that if the number is positive, color it green; negative, color it blue; zero, color it red; and text entries, color them yellow. If you omit one of the conditions, be sure to keep the semicolon.

As for the useful situations: Pranks. Add colors to your co-workers number formats and watch as they hilariously check Conditional Formatting. Yeah, Excel guys really know how to have a good time.