Repeat conditional formatting across rows in a Living Pokédex

viampravam

New Member
Joined
Sep 17, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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?

1631879263766.png
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

TheCobbler

New Member
Joined
Aug 21, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 17, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.

1631882390881.png


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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,992
Office Version
  1. 365
Platform
  1. Windows
You need to use
Excel Formula:
=$L5="t"
that way it will work for every row.
 
Solution

viampravam

New Member
Joined
Sep 17, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
You need to use
Excel Formula:
=$L5="t"
that way it will work for every row.
It seems to be working perfectly now!

1631883117940.png
changed to
1631883144317.png

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

Thank you!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,992
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,148,241
Messages
5,745,583
Members
423,963
Latest member
lwilson3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top