Find Next Red Cell in Column, Return Rownumber of that Cell

Question

Im looking for a function/macro that finds the next red cell in column A on my worksheet. Basically what I wanna do is Loop through Column A and everytime I find an empty cell, jump down to the next red cell and continue the loop there. All I need is the row# of the next red cell in order to do thuis. What I have working so far is this:

'Loop through column A from top down starting in row 6 and create Chart for each row
For rownumber = 6 To LastRow Step 1

'If Cell is filled
If TPsheet.Cells(rownumber, 1) <> "" Then
'Create Chart
Call CreateChart()
Else
rownumber = rownumber + 2 (This is the problem area, just going down 2 rows sadly doesnt work as sometimes there are 3/4/5 consecutive empty rows, need to set this to rownumber of next red cell)
End If

Next rownumber

The whole table looks like this: And the amount of empty rows between the different red tables sections can vary, which is why just going down a certain amount of rows when it finds an empty one (my current approach) doesnt work.

For findind a red cell I successfully use this in antoher macro:

Do Until i = 1000
    If TPsheet.Range("A" & i).Interior.Color = RGB(255, 0, 0) Then
       '...  
    End If
i = i + 1
Loop

TLDR: Need the rownumber of the next red cell in column a while looping through the column when a cell is empty in order to jump to the next red table header

score 3 · Accepted Answer · answered Mar 30 '21 at 14:10

You can use the build-in Excel Find-function to search for formatting. The following function will return the next cell having a specific back color or Nothing if no cell can be found below a certain point.

Function FindColoredCell(ws As Worksheet, searchColor As Long, Optional afterCell As Range = Nothing) As Range
    If afterCell Is Nothing Then Set afterCell = ws.Cells(1, 1)
    ' Set the search parameter for the specific color.
    With Application.FindFormat.Interior
        .Pattern = xlSolid
        .color = searchColor
    End With
    ' Do the search
    Dim cell As Range
    Set cell = ws.Cells.Find(What:="", after:=afterCell, SearchDirection:=xlNext, SearchFormat:=True)
    If cell Is Nothing Then Exit Function           ' now cell found with color
    If cell.row < afterCell.row Then Exit Function  ' now cell below "afterCell", Search started at top
    Set FindColoredCell = cell
End Function

(if it fits your need better, change return value to Long and return cell.Row)

Note: In your code, you are using a For-Loop. Don't do that if you will modify the counting variable (in your case rowNumber) within the loop or you will get unpredictable results. Use do Do While-Loop instead.

Thank you so much, this is really smart! When making the mentioned changes to `Function FindColoredCell(ws As Worksheet, searchColor As Long, Optional afterCell As Range = Nothing) As Long` (long instead of range) and `Set FindColoredCell = cell.row` I get an "Object required"-Error on cell.row though? Any ideas how to fix this? Thank you so much — Leon, Mar 30 '21 at 14:43
You may read `cell.row` only if `cell` is not nothing (with other words, when a colored cell was found) (I have `Exit Function`-statements in my example, in that case 0 will be returned). — FunThomas, Mar 30 '21 at 14:46
Changed .color to .colorIndex and used 3 as long for red and now its working like a charm :) Thank you! — Leon, Mar 30 '21 at 14:59

score 1 · Answer 2 · answered Mar 30 '21 at 13:40

1

This will take a lot of time if you are already over the last red line.

Function GetNextRedInteriorRowNumber() As Long

Dim i As Long
Dim c As Long
Dim retVal As Long

retVal = -1
c = ActiveCell.Column

For i = ActiveCell.Row + 1 To ActiveSheet.Rows.Count
    
    If ActiveSheet.Cells(i, c).Interior.Color = RGB(255, 0, 0) Then
        retVal = i
        Exit For
    End If
Next

GetNextRedInteriorRowNumber = retVal

End Function

answered Mar 30 '21 at 13:40

Francesco Giossi

158
9

2

Note: probably better to [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) instead of looping all the way to the bottom of the sheet. – BigBen Mar 30 '21 at 13:52
Thanks! Also implemented the Last-Row from Ben :) – Leon Mar 30 '21 at 14:33

Find Next Red Cell in Column, Return Rownumber of that Cell

2 Answers2