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.
Posting code? Use <pre> tags for VBA and <code> tags for inline.