It depends on the words also. In general, one has to be careful with Pesukim, but Kaddish and Shema are more problematic if the tunes aren’t appropriate, imho (Shema is not my opinion – it’s brought down somewhere, but I haven’t specifically seen anything about Kaddish – it just seems obvious).
The main question is – does the song (combination of words and tune) bring you closer to Hashem? Does the tune help you to internalize the meaning of the words? Different people will have different answers for different songs. It also depends on sensitivity levels.
Personally, I’m not as sensitive as some are, but I’m more sensitive than others. I like music that is somewhat modern and rocky – it makes me happy and gives me energy- but I don’t like it when it is too much so and especially, if it doesn’t fit the words.
I feel like sometimes people just throw together tunes and words that have nothing to do with each other.