depending If the players appears within a certain range

I would appreciate a bit of help with conditional formatting the colours of cells depending If the players appears within a certain range. This will show me quickly which players have been playing recently

1. range BG2 : BG12 then I would like the column N to represent this with changing the color to Green with a ?T? inside.
2. range BG14 : BG24 then I would like the column N to represent this with changing the color to Orange with a ?S? inside.
3. range BG26 : BG31 then I would like the column N to represent this with changing the color to Orange with a ?S>? inside.
4. range BG33 : BG39 then I would like the column N to represent this with changing the color to Green with a ?T>? inside.

I would then like to repeat this for all of the columns BG - CN to fill in the cells N to AU

Hi JeteMc, thanks very much for the reply. I have updated my profile, i am on Microsoft 2023 Excel Version 16.73, not 2003 like my profile had!

I´ve checked the spreadsheet and parts 1 & 2 work perfectly. Although I made a mistake with putting ?T? as it should just put T. which I have corrected in the attached excel document

1. range BG2 : BG12 then I would like the column N to represent this with changing the color to Green with a "T" inside.
2. range BG14 : BG24 then I would like the column N to represent this with changing the color to Orange with a "S" inside.

Unfortunately the Columns don´t update for the following 3 & 4
3. range BG26 : BG31 then I would like the column N to represent this with changing the color to Orange with a "S>" inside.
4. range BG33 : BG39 then I would like the column N to represent this with changing the color to Green with a "T>" inside.

The reason I need this is to show which players started the games "T" but then were subbed out "T>" (Part 4) and which substitutes didn´t enter the pitch "S" and which substitutes were subbed onto the pitch "S>" (part 3). Although this spreadsheet only includes players who were subbed in, I will have future spread sheets where players travelled as part of a matchday squad but didnt enter the pitch and this is information which I would like to present here when I have more information.

As an example i have put column BG some extra players highlighted in blue. When converted using the formula those players should have a Yellow cell and an S. The other substitute who entered the pitch should have a yellow cell and S>

I would appreciate it if you help me iron out this detail also.

Many thanks

Thanks alot, thats the perfect solution.... thank you!