What is it, and why does it seem to have a cult following? All I know about it is it features Tim Curry in drag.

It is the ultimate "midnight movie". It is, to the best of my knowledge, the only ever made comedy science fiction horror adult low budget musical movie. And it's absolutely brilliant :-D
The history of the thing is very interesting. While the movie itself probably only appeals to a narrow segment of people (such as myself) the story behind it should be interesting to anyone, as the birth of a cultural phenomenon.

It started as a stage production and was made into a film in the mid 70s. It's got a quality soundtrack and a quite frankly ridiculous storyline, here is more info:
http://en.wikipedia.org/wiki/The_Rocky_Horror_Picture_Show
http://www.imdb.com/title/tt0073629/
The stage show is still running here in the UK as well:
http://www.rockyhorror.co.uk/v1/

It's a cult film/musical, best watched late at night with a few others as it allows one to play in an audience participation role. If you are so inclined to do so.
One of the local pay-tv stations a couple of years back decided it could not compete with the others on a holiday. What did they do? Run Rocky Horror, in a loop for 24 hours in a Time Warp special. If anyone sat through that, they need a medal.
Personally, I prefer Little Shop of Horrors for an odd musical film choice :)

...and thrown the rice, and the flowers, and the water, and the toilet paper, and the KitKat, and the toast, and the playing cards...
Man, I need to find a cinema showing Rocky Horror real soon.

Maybe when I get older, I can watch this while stoned and drunk, and maybe the movie will finally make sense.

I don't think it ever makes sense.

rhps is not something that makes sense, it simply IS! And it's awesome. And it's a rdug in itself so you dont' need drugs to watch it..watch it, and you're as good as drugged already!