We've done two New England fall foliage cruises and have found that the leaves along the coastline turn colors weeks later than those a few miles inland. So if your cruise is at the peak of color for shore excursions, the leaves you see from the ship will still be green. Since the Canadian ports you'll visit are further north, the leaves will change colors before those in the U.S. ports. Hopefully you'll hit the "peak" somewhere along the way, but don't expect to see Trix colors (raspberry red, lemon yellow and orange orange) the whole time. And of course each year is a tad different. On one of our cruises the leaves were at their peak, or a bit past. On the other cruise on similar dates, the leaves were just starting to turn.
In other words, I wouldn't get a balcony just to see more colorful foliage. Tho I do love stepping out onto the balcony for some fresh air.