We often conflate the two basic tiers of cult membership when we talk about various cults.
Lay member. This is casual worship of a deity. You make offerings, you offer prayers, and participate in the outer rituals of the cults. Many people are lay members of lots of deities. You can usually leave lay membership with few consequences. Heck, in many cults it is expected that you only make offerings as a lay member when you need something (like training or spirit magic).
Initiate. This is a deep personal and spiritual tie with one deity. It is not casual – you’ve participated in the secret mysteries of the deity. You can even wield the power of the god through Rune magic. Leaving the cult as an initiate is a SERIOUS matter. Most cults have spirits of reprisal that will attack an apostate at an inopportune moment – and you can even lose all your Rune magic.
Once you are an initiate, you should only join other cults as a lay member if it is at least neutral towards your cult – joining a hostile cult as a lay member can trigger the spirits of reprisal. And if you want to initiate into another cult, it MUST be at least friendly towards your cult.
So for example, it is perfectly acceptable to an Orlanth initiate to become an initiate of any of the Lightbringers (they are closer than friendly – they are associated cults) or the Humakti (who is friendly). You can be a lay member of Yelmalio, but you cannot become an initiate of Yelmalio while remaining an initiate of Orlanth.
Now as an initiate of Orlanth you might be willing to tempt the fates and offer worship to the Seven Mothers as a lay member. Depending on your priests, that might trigger spirits of reprisal, but it might not in occupied territories. However, you cannot initiate to the Seven Mothers without first leaving the Orlanth cult as an initiate.
And worship of the Red Goddess is outright – even as a lay member – is going to trigger those spirits of reprisal. She’s associated with Chaos and is not merely hostile towards Orlanth, she’s an enemy.