Conditional Formatting - Based on Criteria

My worksheet is attached. I have two sheets inside it, on the second sheet, I would like cells B2:B30 to shade based on the information entered in Sheet 1.

Using the three criteria in column B of sheet 1, each has a number entered. This number will change however when someone edits that they want more emphasis on hitting than pitching and vice versa so I believe it needs to have a formula of some kind.

As it stands now, cells B2:B15 should have a red background because they are hitting spots, cells B16:B24 should have a blue background because they are pitching spots and the remaining six cells should have a gray background because they are bench spots.

Now if someone were to change the hitting number to 12 and the pitching number to 11 I would want those change to automatically take place in the shading on Sheet 2. So cells B2:B12 would now be red and cells B13:B24 would now be blue. Always have Hitting at the top, pitching in the middle and bench at the bottom.

Any help would be greatly appreciated and thanks for your time!

Originally Posted by protonLeah

grey: =COUNTIF(B$2:B2,">" & hitting+pitching)
blue:=AND(COUNT(B$2:B2)>hitting,COUNT(B$2:B2)<=hitting+pitching)
red:=COUNT(B$2:B2)<=hitting

c3: named range "hitting"
c4:named range "pitching"
c5:named range "bench"

Wonderful thank you!