Can't help you with the Great Lakes but Vancouver Island undoubtedly has the best cold-water (scenic) diving in the world. There's so much colour and life you'd think you were on a coral reef, except that the temperature is about the same as in the UK. I've been twice, the first time a week each at Rendezvous Dive Ventures (Barkley Sound, on the west side of Vancouver Island) and God's Pocket Resort (Hurst Island, just off the northern tip of Vancouver Island) and the second time on a liveaboard (Nautilus Explorer) from Vancouver to the Queen Charlotte Islands and back. Rendezvous and God's Pocket are both excellent divecentres. You'll find their websites if you do a Google search. Next September I'm taking a group to Dynamike Diving, a divecentre on Quadra Island, near the small town of Campbell River. We did a couple of dives in that area on the way back to Vancouver after the QCI trip and thought the diving was the best we'd experienced in British Columbia. Friends who have been there, including Lou on these boards, speak very highly of Dynamike Diving. If you do a search, you should find Lou's trip report from last October on the Recent Trips board. Dynamike's web address is http://www.divedynamike.com
The best time to go is in September, by the way. There si often a heavy plankton bloom in the summer months.