I'm waiting a bit for the mods to "mature" a little. Give it a few months, and people will have sorted through the chaff and we'll be left with the good ones, and the lists will pop up of the must-have mods. That's probably when I'll get in on it.
The water one looks real nice, though. Almost looks like he just decreased the effective size of the effect that Bethesda already had, but it does the trick. That last shot underwater is a little weird, though.
I'm not a big fan of most of the mods that we've seen so far. Some of the "HD texture" mods aren't just upping the resolution, they're changing the art style, which is a big no-no for a game like this, IMO, where the art style is what really makes it shine, in spite of low-res textures. And there's a few that do a bit too much fiddling with the image, again ruining the look of the game. I've also seen a few where they're just plain lazy.. extracting the textures, running a Photoshop sharpen filter on them, then reimporting them and calling them improved.