Marvel's Death of Captain America storyline in 2007 was more than a highly-regarded story in the aftermath of one with a more mixed reception (Civil War) - it was the last time that a superhero's death actually carried weight. After witnessing countless superheroes die only to be resurrected - again and again - many fans of the medium are no longer moved or even remotely emotionally swayed by the latest hero shuffling off the mortal coil. The Death of Captain America avoided the problem by arriving at a perfect moment in American history.
Even in the early days of comic books, death was more of an inconvenience than a point of no return. Characters died often - but they were mostly C or D-list characters and rarely a major hero that carried an entire book, such as Batman or Spider-Man. Only characters who died during a hero's origin story would remain dead, such as Uncle Ben, Thomas and Martha Wayne, and Doctor Ho Yinsen. When Stan Lee brought Captain America back to Marvel Comics after a decade's hiatus, he memorably killed Bucky Barnes to give Rogers some much-needed pathos (and because Lee despised the trope of the teenage sidekick). This death was intended to be permanent (but Bucky Barnes returned in 2005).
In the aftermath of Marvel's Civil War event, Captain America surrendered after a long battle between his anti-registration faction and Iron Man's own pro-registration force. As Captain America is taken into custody while walking up the steps of the Federal Courthouse, he's shot by a sniper and quickly collapses. Heroes immediately surround Rogers, but it's too late to save him; Captain America is dead. The death is rather "realistic" by comic book standards; Captain America isn't killed by a world-ending monster, doomsday weapon or evil sorcerer, but by a regular gun. This larger-than-life hero is only a man, as mortal as any other.
The method of execution was not the only reason why the death of Captain America mattered. 2007 was a tumultuous year for the United States on the world stage; American troops were still stationed in Iraq seemingly without a plan or purpose, the country was in the beginning stages of events which would ultimate lead to the Global Financial Crisis of 2008, and the unpopular Bush Administration was the source of ridicule and hatred from the rest of the world. It's no accident that the story in which Steve Rogers was killed was titled Death of the Dream, because for some, the American Dream was just that: an unattainable fantasy built on the always-unstable foundations of American Exceptionalism.
Captain America would, of course, return to comics a year after his passing, but the story's emotional core was never about the loss of the man, but rather the myth. Captain America was created before World War II in response to Nazi aggression (and anti-Semitic attacks on the homefront), but America was no longer seen as the world's savior (and perhaps it never was). Captain America's death mattered because the creative team realized that the country for which Steve Rogers fought was far from perfect; all that was left to convey the message was to kill the perfect man.