Very few honors programs offer tangible benefits that amount to anything more than small perks - it’s something many colleges use as an ego boost to attract applicants. It does mean you’ll be surrounded by smarter students than the general population often, if that’s a big consideration. You’ll get slightly more focused advising, some housing perks, registration priority at SB, etc.
Make sure you also look at any additional curriculum requirements - it can actually be worse for some in terms of letting them take the classes they want outside of their major, particularly for STEM majors. I know that I actually would not want to be in the honors program at Northeastern because of that.
Long story short: Honors is generally not a big deal unless you highly value those perks, except for a few exceptions at certain colleges.