I've kind of been feeling motivated to play a new game n I've never played a heroes game n was wondering which ones are good. I'd say I wouldn't like the very ancient ones as I didn't like the MM games before 6 either.

I still play HOMM III.
Liked the campaign in V. But III is till fun to play

I didn't really play HoMM 1 very much.

HoMM 2 is good, but its very slow paced and old. Great quality on campaigns and soundtrack.

HoMM 3 is the best in the series and is very good and very playable to this day. If you buy it through GOG, you get the HoMM 3 Complete edition, which is the best edition by far, as it lets you mod the game with the HD mod and Horn of the Abyss (HotA).

The HD mod adds a lot of quality of life changes, such as quick stacking/unstacking you army, letting you complete all fights with auto combat and then deciding if you keep the results or if you want to fight manually (major time save if your army is completely dominant and smaller armies attack you).

You want the HoMM 3 complete edition, NOT the "HD Edition". You want the HD mod, but not the HD ediiton, as it can't be used with the HD mod or HotA.

HotA is a russian fan-made mod that is just as good (and big) as any official expansion set. It adds a ton of content, including an additional faction, lots of artifacts and balance changes.

HoMM 4 is decent. It's the last game made by 3DO/NWC (though with key people from earlier titles missing from the credits). It features a soundtrack by Paul Romero (the guy who did a lot of the MM/HoMM music up to that point) and it's just as good as you can expect, possibly better IMO.

I tried a couple of the later heroes games, but none were my cup of tea, and they are very different from HoMM 1-4.