Baseball Position Selection Chart

Hi everyone,

I have been looking for a way to modify a positional spreadsheet for our softball team so that based on each inning as I highlight a position, it will highlight that same position in another colour for the rest of the names on the team. For example: If I highlight '1B' for the 1st inning beside Dusty in yellow, all of the other '1B' beside the rest of the names in the 1st inning will be highlighted red. The goal for this exercise is to prevent selecting duplicate positions on an inning by inning basis.

Any help would be greatly appreciated!

Thank you!

This is kind of a tall order, but it would help if you could provide the actual spreadsheet. I doubt anyone is going to take the time to reproduce it. And instead of color, would it be OK to replace the position with an "X". The reason being is that Excel can't fire an event based on a change in color. However, if you are willing to push a button, then you can color the cell and push the button or map the macro to a Control Key such as CTRL-W or some other seldom used combination).

It helps that the format is rigid this makes referencing other cells easier: it can be done with formulas and offsets.

I think I got this one. It was fun to put together.

Start off by clicking CTRL-W. This will clear all assignments. This also fills an array with the positions and clears out all previous assignments. So you should do this before using the program.

Warning: the program is very sensitive to the way the data is laid out. If you insert rows or columns, you may break it. You can add additional players at the end as long as you keep the same structure for each player. Also make sure the positions are all spelled consistently. For example, RF had a following space. I’ve corrected all that for this spreadsheet.

Basically if you copy and paste a player, it should work.

To assign a player, select the cell and press CTRL-Q. This will shade the cell to yellow and shade all other cells for the other players in the same inning to red.

If you change a player from say 2B to SS, the blocks recalculate. So 2B opens up for other players and SS is shaded yellow for the player.

Likewise, if Eric were pitching and you call in John H, when you select John H and press CTRL-Q, John H will be shaded Yellow and Eric (and everyone else) will be shaded red for the pitcher position.

Yes it is. The highly-organized structure of the sheet made it a lot easier to accomplish: it took a bit of high-school algebra to figure out which "box" a selection was in and to loop through the players. If you really want to see what's going on "under the hood" press ALT-F11 and it will bring up the VB editor. I have some comments in the code that kind of explain what I was trying to do.

Good luck with the team.