So I manged to get this working a few days ago. I tried many different approaches, and none of them worked. Just in case anyone else stumbles on this thread looking for a solution. Here is what I did:
I used a scoreboard set to detect the used_rod stat then execute on that to call a sub function for the player to tag the bobber.
execute as @a[scores={use_rod=1..}] at @s if block ~ ~-1 ~ red_wool run function game:misc/red_cast
Execute at the player and look for a bobber that is within 3 blocks and not already tagged by another player.
execute as @s at @s as @e[type=fishing_bobber,distance=..3,limit=1] run tag @e[type=fishing_bobber,tag=!red_bobber,tag=!gold_bobber,tag=!green_bobber,tag=!purple_bobber,limit=1] add red_bobber
Reset the use_rod stat to 0
Since I use various game states to manage various aspects of the game, I have a function that runs on the tick and detects when the bobber is near the sheep then runs a sub function for handling the score keeping part.
execute as @e[type=fishing_bobber,tag=red_bobber] at @s if entity @e[type=sheep,distance=..1.09] run function game:misc/caught_sheep_red
I had to do it this way, because I needed to execute as the bobber first then pass it to the caught_sheep function so the bobber could be referenced with the @s selector.
There is probably a better more elegant way to do it but this is how I got it working