The Red Arrow is the token that Denethor uses to call Théoden and his allies into battle against Sauron. We can guess that it's a sign of extreme desperation because when Théoden receives it from a messenger, his hand trembles and he exclaims, "The Red Arrow has not been seen in the Mark in all my years! Has it indeed come to that?" (5.3.59).
Well no wonder Denethor's desperate. The end is threatening Minas Tirith if the Red Arrow has been sent out. Sadly, Théoden, being a good guy and all, tries to reply to Denethor's summons, but the messenger carrying the Red Arrow back to Gondor is killed before he can deliver the message. That means Denethor has no clue that Théoden's about to pitch in. We can't help but wonder if Denethor's despair would have been less extreme had he known that the Riders of Rohan were doing their best to answer his summons.