Module weights sometimes come into play when you're trying to override certain aspects of the core or other modules. If you look in the Drupal database's system table you'll notice a field called weight - this is what determines the order in which all of the installed modules' hooks will get called during a page request.

The general rule of thumb is that if you want to make sure your custom module gets last dibs on things like hook_form_alter() then you should be sure to give it a heavier weight in the system table, either by a direct SQL query in your module's install file, or by using a module like moduleweight (D5) or Utility (D6).

As I learned this morning, the inverse applies if you're trying to override menu items, at least in Drupal 5. In my case I was trying to change the weights of some MENU_LOCAL_TASK menu items, as well as alter which one would be used as the default, but all of the changes I had made in my implementation of hook_menu() were getting ignored. On a hunch I tried making my custom module lighter than all the others, and sure enough my menu changes took precedence - apparently the first module to declare a menu item gets precedence.