Baseball / Hitting Streaks - Conditional Formating

CrimsnTide · Feb 5, 2019

Below is a snapshot of my spreadsheet. All numbers (hits) are populated (linked) from other sheets in workbook depicting each specific games. The GREEN background seen is currently being formatted manually. I am now trying to figure out how to CONDITIONALLY FORMAT the cells to show a players "hitting streak".

Conditional Formatting is based on the adjacent cells number. To be formatted in GREEN, a player needs to have consecutive games with a hit. A game with a ZERO (0), means he had a "official" AT BAT, but did NOT get a hit. The cheesy format I came up with is (starting with #7): =AND(D14>0,OR(D13>0,D15>0,))

My problem is this: I cannot figure out how to calculate the games where they did NOT get a official At Bat. (See player #11 on 3/26, 3/27 and 3/28. Player #11 MISSED the game on 3/27, - so no OFFICIAL AT BAT. (Names removed to protect the “team”…) The same scenario for Player #21 on days 3/5, 3/6 and 3/8. On 3/6, he did NOT bat, but got hits on both 3/6 and 3/8. Therefore, the hitting streak continues.

I'm trying to play with ISBLANK, and ““ for the blank days, but just can't seem to get it to work. To clarify and to add a a hypothetical situation, a player could come to bat on the very FIRST game of the season, not play again until the last game of the season and get hits in both games. He “technically” has a 2-game hitting streak. There is a difference between a cell having a ZERO - or being BLANK.

It would be nice to just have a conditional format rule rather than to un-protect the sheet every time I wish to show highlight a hitting streak…. Hopefully I explained my dilemma.

Conditional Formating for the Multiple Hit games was easy - Anything over 1 is formatted to BOLD and the text is colored red.

[/IMG]

CrimsnTide · Feb 7, 2019

Still playing with it. I just can't seem to account for the blank cell (a game with zero at bats), but with a hit the day before and the day after. (Which would continue the hitting streak).

Maybe just calculating the number of CONTINUOUS games with a hit. A cell populated with any number >1 gets counted. A ZERO would break the streak (counting), but a BLANK CELL enables the streak to continue (but is NOT COUNTED). I can get away with just a cell to calculate the hitting streak for each player, I can maybe get away with that....

Scott T · Feb 7, 2019

Try this code on a copy of your data.

Code:

Sub basball()
Dim lr As Long
Dim lc As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(2, 3), Cells(lr, lc)).Interior.ColorIndex = -4142
For x = 2 To lr
    For y = 3 To lc
        If (Cells(x, y) >= 1 And Cells(x, y + 1) >= 1) Or (Cells(x, y) >= 1 And Cells(x, y - 1) >= 1) Then
            Cells(x, y).Interior.ColorIndex = 4
        End If
        
        If Cells(x, y) = "" Then
            l = Cells(x, y).End(xlToLeft).Address
            n = Cells(x, y).End(xlToRight).Address
                If Range(l) >= 1 And Range(n) >= 1 And Range(l).Column <> 1 Then
                    Range(l).Interior.ColorIndex = 4
                    Range(n).Interior.ColorIndex = 4
                    Cells(x, y).Interior.ColorIndex = 4
                End If
            
        End If
        
    Next y
    
Next x
 
End Sub

Baseball / Hitting Streaks - Conditional Formating

CrimsnTide

New Member

Excel Facts

CrimsnTide

New Member

Scott T

Well-known Member

Similar threads

Forum statistics

Share this page

Baseball / Hitting Streaks - Conditional Formating

CrimsnTide

New Member

Excel Facts

CrimsnTide

New Member

Scott T

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock