Color Rows not filling in correctly

[Darren Smith]

I created a VBA code to fill in color to rows which works fine except for some reason all the rows don't fill in example row 22 and row 27. Rows 22 & 27 have text in row below in column C
Then row 26 does for some unknown reason?
The Column C has text in so the row above the text should fill with color.

Private Sub Add_Break_Lines_Click()

    Dim cmb As ComboBox
    Dim ws As Worksheet
    Dim Lastrow As Long

    Set ws = ThisWorkbook.Worksheets("Job Card Master")
    Set cmb = Me.Add_Break_Lines
   
    Lastrow = ws.Cells(Rows.Count, 3).End(xlUp).Row

    ws.Range("P13:P299").ClearContents
   
    Select Case cmb.Value
   
          Case ("Break Lines 1 Page Job Card")
                        colorAbove ws.Range("A13:Q"& Lastrow)
           
          Case ("Break Lines 2 Page Job Card")
                        colorAbove ws.Range("A13:Q61")
                        colorAbove ws.Range("A66:Q" & Lastrow)
                       
          Case ("Break Lines 3 Page Job Card")
                        colorAbove ws.Range("A13:Q61")
                        colorAbove ws.Range("A66:Q122")
                        colorAbove ws.Range("A127:Q" & Lastrow)
                       
           Case ("Break Lines 4 Page Job Card")
                        colorAbove ws.Range("A13:Q61")
                        colorAbove ws.Range("A66:Q122")
                        colorAbove ws.Range("A127:Q183")
                        colorAbove ws.Range("A188:Q" & Lastrow)
                       
           Case ("Break Lines 5 Page Job Card")
                        colorAbove ws.Range("A13:Q61")
                        colorAbove ws.Range("A66:Q122")
                        colorAbove ws.Range("A127:Q183")
                        colorAbove ws.Range("A188:Q244")
                        colorAbove ws.Range("A249:Q" & Lastrow)
    End Select

    Me.Add_Break_Lines.Text = "Add Break Lines"
   
End Sub
Sub colorAbove(rng As Range)
   
    Dim brg As Range
    Dim rrg As Range
    Dim EmptyRowNum As Long
    Dim i As Long
   
    For i = 1 To rng.Rows.Count
        Set rrg = rng.Rows(i)
        If WorksheetFunction.CountA(rrg) = 0 Then
            EmptyRowNum = EmptyRowNum + 1
        End If
        If EmptyRowNum = 2 Then
            EmptyRowNum = 0
            If brg Is Nothing Then
                Set brg = rrg
            Else
                Set brg = Union(brg, rrg)
            End If
        End If
    Next i
   
    If Not brg Is Nothing Then
        brg.Interior.ColorIndex = 36
    End If

End Sub

 

[NoSparks]

It doesn't fill row 22 because there are not 2 blank rows above C23.
It fills 26 instead of 27 because it's still counting the one blank line that is above C23 when it gets to row 27
Then the blank of 27 goes with the blank of 40 for the belief of 2 blank rows above C41

 

[Darren Smith]

Thanks but how do I get over this issue

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Lines filling above filled in cells in column C - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Darren Smith]

Sure I see
Can you help with the question?

 

[Fluff]

Please supply links to all other sites where you have asked this question, as requested.

 

[Darren Smith]

Here you are

Lines filling above filled in cells in column C - Excel VBA / Macros - OzGrid Free Excel/VBA Help Forum

excel - Colour into range rows issue - Stack Overflow

 

[NoSparks]

Thanks but how do I get over this issue

Just loop down column C for non blank cells and check if the immediately preceding row is blank.
No need to be counting blank rows as you now do in the colorAbove macro.
And no need to be Unioning ranges just color the row.

 

[Darren Smith]

How can I loop down column C to check if the immediately preceding row is blank?

 

[NoSparks]

post the code that you've tried

 

[Darren Smith]

I have not tried a code and can`t seem to find out how to by surfing the internet