Auto Populating Chart

You don't need VB, but you do need to revise the data.

Sheet2 shows "normalized" data in the table. To make a table, simply click in the data range, select the home tab and format as a table. Tables have the advantage that they know how big they are, so when you add or delete rows, formulas based on them automatically reference exactly the right amount of data. Also formulas written against tables use header names which makes them easier to understand.

I populated the percentages with random numbers for the purposes of testing. I don't know how you are populating this data.

I made another table called Table_Card to assign percentages to card colors. I used this to figure out what color card to assign on the main table. The helper column E (Card) has the formula: =VLOOKUP([@Percentage],Table_Card,2,TRUE),

You can see how using tables make the formula easier to read. You are probably used to seeing the VLOOKUP formula with the FALSE option which means give me an exact match or give me #N/A. The True option works when the data you are looking up on is sorted lowest to highest. The way it works is if an exact match is found it will take it. Otherwise it "falls back" the then next lowest level. So 91% falls back to 90% (Gold), 90% is an exact match (Gold) and 89% falls back to 70% (Blue). The TRUE option is a good way to put things into "Buckets" or "Grades" without having to write complex IF statements.

Then I created a pivot table using the main table as the source. I have Card as the Filter, Name and Rank as the Rows and Percentage as the Values. I made it tabular with no sum and no subtotals. Then I copied it twice and set the three filters to Gold, Blue and White.

Now you can enter data as you wish in to the table. You can even erase all the rows (select them, right click and select Delete -> Table Row) and fill in with new data. The table also "remembers" the formulas.

When you change data, you will have to refresh the have to refresh the pivot table. Since each pivot table is based on the same data set, refreshing one refreshes them all.