I think unfaithfulness, temptation, and everything that goes with them can add complexity to characters, even the shiny hero. Promiscuity, infidelity for their own sake don't do much for me, but they can add wonderful dimensions to character and plot. But then, I'm not an HEA fundamentalist, either in reading or writing.