On Pilot, the Sport mode button only alters transmission operation. Some Acura models have IDS ( Integrated Dynamics System ) settings that influence other driving parameters such as power steering assist and sometimes shock absorber stiffness, but no Honda Pilot has this.
Sport mode and Sequential mode are not the same. Sequential mode is accessible from Sport mode. Sport mode alters the shift points to keep the engine in its power-band, at higher rpm. This enhances responsiveness at the expense of fuel economy, but it is fully automatic. Pulling a paddle shifter on the steering wheel while in Sport mode puts the transmission in Manual Sequential mode, in which the transmission will shift up or down one gear at a time in response to the paddle shifters ( hence sequential ). Once activated, it will remain in that mode until the Sport/Drive button or another shift button is pressed. The controller is smart enough to intervene under conditions that would lug the engine or over-rev it. Manual Sequential mode is also accessible from Drive, but it is temporary and the transmission controller will revert back to normal Drive automatic operation when you chill and stop yanking paddles.