Repeat conditional formatting across rows in a Living Pokédex

[viampravam]

Hi all, I'm trying to add some formatting to a living Pokédex (Pokémon I have in my inventory in Pokémon GO) I'm keeping in Excel. I did manual highlighting of cells based on their content early on, but then I found out about conditional formatting and it's been a great tool thus far.

A quick description of the context:
A Pokémon in Pokémon GO has three stats (Attack, Defense, and HP) which all have values from 0 to 15. A Pokémon with the stats 15-15-15 is so-called "perfect", and those are the ones I wish to highlight.

I've managed to add rules so that any stat cell with 15 in it changes to a darker background than the default shade. Now I'd like to change the font colour of the stats for any perfect Pokémon. Meaning that if the three cells all contain 15, I want them to have both a darker background shade, and a yellow font colour. I've added a screenshot of the font colour formatting I did manually for reference.

I made a few attempts to format the font colour based on formulas but without success. I made it work when I based the formatting on a fourth cell in column L (not visible in the screenshot but it's there) which contains "t" if the stat sum is 45 and "f" otherwise. If the cell in column L contained "t", then the stat font colour changed to yellow, and remained white otherwise. When I tried to copy/paste the format rule from this functioning row to the next (i.e. for the next Pokémon) however, I discovered that the rule does not change what row it considers when checking that fourth cell. Meaning that all the t/f results were based on the same Pokémon from which I copied the rule.

I therefore wonder if there is any way for me to copy this functional formatting rule to the remaining 900-or-so rows without having to manually change what row of the column L the rule regards? Or do you have any other recommendations on how I can achieve the same formatting?

 

[TheCobbler]

Hi! Nice dex Not an expert but you can change the range the formatting applies to as a start!
Conditional Formatting > Manage Rules. Then changing the range the formatting 'applies to'. You can select the columns.
Hope that's something useful.

 

[viampravam]

Hi! Nice dex Not an expert but you can change the range the formatting applies to as a start!
Conditional Formatting > Manage Rules. Then changing the range the formatting 'applies to'. You can select the columns.
Hope that's something useful.

Hi and thanks for your reply!
That would certainly be easier than copy/pasting! Although the rule still remains the same for that range, it seems.

I guess I wonder if there is a way to have a rule with an unspecified row number, so that it applies to the same row in the yellow-marked field as the row it currently evaluates the formatting for, if that makes sense.

 

[Fluff]

You need to use

=$L5="t"

that way it will work for every row.

 

[viampravam]

You need to use

=$L5="t"

that way it will work for every row.

It seems to be working perfectly now!

changed to

and it returns to the previous state if I change it back. Now to repeat this for the shiny variants

Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.