Superman and Red Sunlight
First, there's really no such thing as 'red sunlight' at least not from the star we call sun. What we call red sunlight is the sun's natural light reflecting off other surfaces (usually the atmosphere) and changing the hue of that light to 'red'. You'll generally see 'red sunlight' at evening or dawn when the sun is parallel with the surface of the Earth, since the light coming in at 0 degrees has to penetrate the entire atmosphere thus producing a noticeable change in hue. With moderate cloud cover you may even see a purple or blue sun at these times-- again the sun isn't changing color, the semi-transparent surfaces the sunlight is shining through change the hue.
Now, for the non-scientific, if I remember the the superman creation story correctly Krypton was in a system with a red sun and none of the Kryptonians were superhuman on that planet, they only get their powers under yellow sun, therefore when the sun turns red Superman turns normal because it's like he's back in Krypton.
Edit: the way the atmosphere changes the color of light is the same way rain can change light thus creating a rainbow.
In simple terms, white light is what we get when all visible colors (wavelengths) of light mix equally. When an object appears red under white light, it's because it absorbs all the wavelengths (color) except red. It rejects/reflects the red wavelength. That's just an aside though.
Concerning red sunlight. Our sun is G-type main sequence star, spectral type G-V. It is a white star, meaning it outputs all visible wavelengths of light fairly symmetrically with the peak in the middle of the visible spectrum.
The arrow is pointing to the entire curve, not a point on it.
The sun appears yellow to us most of the day because the atmosphere scatters the blue light (and purple), shifting the spectrum of the sunlight towards the red end of the spectrum, where yellow is (it shifts the peak of the curve to the right in the image above).
At sunset, the light we see travels through much more of the atmosphere. The blue light (and purple) scatters out. This is like shining a flashlight down into deep water. The water is too dense for the beam of light to make it all the way down. With sunsets, the blue and purple light scatters out and doesn't make it to us as well, but the longer wavelengths, yellow, orange and red, do.
That's all good and well but what about red stars like the one Superman's home planet orbits.
There are red stars. Or reddish stars anyway. For main sequence stars, these are class M stars. And for stars out of the main sequence, these tend to be red giant stars (which our sun will become someday). Their peak output is shifted towards the red end of the visible light spectrum, and they are cooler than other stars (blue = hot, red =cold for stars and flames as well).
Why this would rob Superman of his super powers, we can only speculate, and it might be that when this idea was thought up, the creator didn't have any real scientific explanation, but Superman isn't very scientific to begin with.