Needing a formula to put colours in a spreadsheet | Excel forum

I am working with a spreadsheet that needs to have colour coding applied to three of the columns when the work priorities are different. The spreadsheet is becoming very large and I am trying to work out (unsuccessfully!) how to get colour into a formula. Here's an example of what I have:

Number Type Location Status Priority
001 fixed site A1 Operational 1

The priority can be 1, 2, 3 or 4. I need the Number, Type, and Location to fill with an appropriate colour to match the priority, and to have that colour change when the priority changes.

I think I need a formula for this, but I can't figure out how to add the colour. I do use conditional formatting, but I don't know how to get that the change with the priority changes.

Any advice?

Cheers!

Dee