I wanted to make a row obviously highlighted and time sensitive as it was part of a on-call rotation so prominence in stressful situations is of paramount importance.
- Highlight all cells in the Google sheet.
- Create a custom formula with the highlighting of your choice
- Put this formula as the Format rule
=WEEKNUM(TODAY()) = ($A:$A)
Using this technique puts the highlighting at the bottom of the formatting rules in my case so any additional formatting rules will stick on top of it as desired, but the overall in your face prominence of the highlighted row remains.