pfunkmasterflex


Several of us in our office have a fantasy golf league and we administrate it off-line using an excel spreadsheet (we haven't found a free website that lets us tailor the rules to the way we like it.)

Anyway, it is a salary cap format, meaning that only one golfer can be on one team at a time. To keep track of this, one of our worksheets has a list of all the golfers. That same worksheet also has every team's roster. For all golfers that are on someone's roster, I manually highlight that same name in the master list of golfers so it is easy to see who is taken and who is available.

What I'd like to be able to do is have it automatically highlight that golfer's name in the master list when it appears in the individual team roster's page (and conversely un-highlight the name when the player is dropped).  But I don't know what the VBA code should be.

Below is an example of how it looks:


you can see the master list on the left and the team rosters on the right.  Any help would be greatly appreciated.  Thanks!




Re: Highlighting cells within a worksheet

Andy Pope


Hi,

It's possible with formula.

Select the range A2:D34 or where ever the list of players ends.
The apply conditional formatting. menu Format > Conditional Formatting...

Change the Cell Value option to Formula and use this formula.

=SUMPRODUCT(--($F$3:$R$23=$B2))>1

Set the pattern to be Red

Add another condition with the formula

=SUMPRODUCT(--($F$3:$R$23=$B2))=1

Set the pattern to Green.

The default pattern for a cell should be automatic. Now when a players name appears once in the range F3:R23 they should be highllighted in green. More than once in Red.






Re: Highlighting cells within a worksheet

pfunkmasterflex

Thanks for the help! It seems to work most of the time, but there is the sporadic ones that don't highlight that should be highlighted and vice versa. I'm leaving work now but I'll have time to look into it more closely next week.

Thanks again!






Re: Highlighting cells within a worksheet

Andy Pope

Check for missing/extra spaces in or at the end of names.

Are you using data validation lists for the team selection or are you re-typing or maybe cut/paste