VBA to skip through the conditionally formatted yellow highlighted rows

[Tommy_Oh_Boy]

Hello,

I have a list of data in $, % and texts that are conditionally formatted to adhere certain conditions in columns A thru E.
If the condition is met, the cell is highlighted in yellow.
The list is long, rows A9 through E2000. Thus I'm hoping to create a macro assigned button that finds the highlighted cells and jumps through the rows within 4 columns (A thru D) so that I avoid scrolling endlessly.
And when it reaches the bottom of the row D2000, it starts back from A9 so that it loops.
Can I get some help creating a VBA code for this?
I attached a sample file as an example.

 

[BigBeachBananas]

Wow... thank you so much.... Only problem with the code is that it looks like it only works on the actual highlights, not the highlights from the conditional formatting...
All of my yellow highlights are from conditional formatting, thus code didn't find any cells...

How about this?

Sub FindNextYellowHighlightedCell2()
    Dim rng As Range
    Dim currentCell As Range
    Dim startCell As Range
    Dim found As Boolean
    Dim cell As Range

    Set rng = ThisWorkbook.Sheets("Sheet1").Range("A9:D2000")

    ' Get the currently selected cell
    Set currentCell = ActiveCell


    If Not Intersect(currentCell, rng) Is Nothing Then
        ' If the current cell is within the range, start from the current cell
        Set startCell = currentCell
    Else
        ' If the current cell is outside the range, start from A9
        Set startCell = rng.Cells(1, 1)
    End If

    found = False

    ' Loop through the range to find the next yellow highlighted cell
    For Each cell In rng
        If found Then
            ' Check for yellow highlighting via conditional formatting
            If cell.DisplayFormat.Interior.Color = RGB(255, 255, 0) Then
                cell.Select
                Exit Sub
            End If
        End If

        ' Check if the current cell matches the start cell
        If cell.Address = startCell.Address Then
            found = True
        End If

        ' Move to the next cell in the range
        If cell.Column = 4 Then
            ' If we are at the last column, move to the next row and start from column A
            Set cell = rng.Cells(cell.Row + 1, 1)
        Else
            ' Otherwise, move to the next column
            Set cell = rng.Cells(cell.Row, cell.Column + 1)
        End If
    Next cell

    ' Inform the user if no yellow highlighted cell was found
    If Not found Then
        MsgBox "No more yellow highlighted cells found in the specified range.", vbInformation
    End If
End Sub

 

[BigBeachBananas]

And when it reaches the bottom of the row D2000, it starts back from A9 so that it loops.

What do you mean by this? Do you mean to skip column E and only search from column A:D?

 

[Tommy_Oh_Boy]

What do you mean by this? Do you mean to skip column E and only search from column A:D?

I'm hoping to review the highlighted cells quickly. so if the next highlighted cell is in row 1000, I want to just click a button to automatically scroll down to row 1000 from row 26 right away. Hope this makes sense

 

[BigBeachBananas]

I'm hoping to review the highlighted cells quickly. so if the next highlighted cell is in row 1000, I want to just click a button to automatically scroll down to row 1000 from row 26 right away. Hope this makes sense

Do you prefer to search by column first (i.e. column A1:2000, then B1:2000,...) or row-wise (A1,B1,C1,...A2,B2,....)?

 

[Tommy_Oh_Boy]

Do you prefer to search by column first (i.e. column A1:2000, then B1:2000,...) or row-wise (A1,B1,C1,...A2,B2,....)?

Row-wise please.

 

[BigBeachBananas]

Row-wise please.

Try this...

Sub FindNextYellowHighlightedCell()
    Dim rng As Range
    Dim currentCell As Range
    Dim startCell As Range
    Dim found As Boolean

    Set rng = ThisWorkbook.Sheets("Sheet1").Range("A9:D2000")
    Set currentCell = ActiveCell


    If Not Intersect(currentCell, rng) Is Nothing Then
        ' If the current cell is within the range, start from the current cell
        Set startCell = currentCell
    Else
        ' If the current cell is outside the range, start from A9
        Set startCell = rng.Cells(1, 1)
    End If

    found = False

    ' Loop through the range to find the next yellow highlighted cell
    For Each cell In rng
        If found Then
            ' Check for yellow highlighting
            If cell.Interior.Color = RGB(255, 255, 0) Then
                cell.Select
                Exit Sub
            End If
        End If

        ' Check if the current cell matches the start cell
        If cell.Address = startCell.Address Then
            found = True
        End If

        If cell.Column = 4 Then
            ' If we are at the last column, move to the next row and start from column A
            Set cell = rng.Cells(cell.Row + 1, 1)
        Else
            ' Otherwise, move to the next column
            Set cell = rng.Cells(cell.Row, cell.Column + 1)
        End If
    Next cell

    If Not found Then
        MsgBox "No more yellow highlighted cells found in the range.", vbInformation
    End If
End Sub

 

[Tommy_Oh_Boy]

Try this...

Sub FindNextYellowHighlightedCell()
    Dim rng As Range
    Dim currentCell As Range
    Dim startCell As Range
    Dim found As Boolean

    Set rng = ThisWorkbook.Sheets("Sheet1").Range("A9:D2000")
    Set currentCell = ActiveCell


    If Not Intersect(currentCell, rng) Is Nothing Then
        ' If the current cell is within the range, start from the current cell
        Set startCell = currentCell
    Else
        ' If the current cell is outside the range, start from A9
        Set startCell = rng.Cells(1, 1)
    End If

    found = False

    ' Loop through the range to find the next yellow highlighted cell
    For Each cell In rng
        If found Then
            ' Check for yellow highlighting
            If cell.Interior.Color = RGB(255, 255, 0) Then
                cell.Select
                Exit Sub
            End If
        End If

        ' Check if the current cell matches the start cell
        If cell.Address = startCell.Address Then
            found = True
        End If

        If cell.Column = 4 Then
            ' If we are at the last column, move to the next row and start from column A
            Set cell = rng.Cells(cell.Row + 1, 1)
        Else
            ' Otherwise, move to the next column
            Set cell = rng.Cells(cell.Row, cell.Column + 1)
        End If
    Next cell

    If Not found Then
        MsgBox "No more yellow highlighted cells found in the range.", vbInformation
    End If
End Sub

Wow... thank you so much.... Only problem with the code is that it looks like it only works on the actual highlights, not the highlights from the conditional formatting...
All of my yellow highlights are from conditional formatting, thus code didn't find any cells...

 

[Fluff]

Once again cross-posted without links.
Please supply links to any other site where you have asked this question.

 

[Tommy_Oh_Boy]

How about this?

Sub FindNextYellowHighlightedCell2()
    Dim rng As Range
    Dim currentCell As Range
    Dim startCell As Range
    Dim found As Boolean
    Dim cell As Range

    Set rng = ThisWorkbook.Sheets("Sheet1").Range("A9:D2000")

    ' Get the currently selected cell
    Set currentCell = ActiveCell


    If Not Intersect(currentCell, rng) Is Nothing Then
        ' If the current cell is within the range, start from the current cell
        Set startCell = currentCell
    Else
        ' If the current cell is outside the range, start from A9
        Set startCell = rng.Cells(1, 1)
    End If

    found = False

    ' Loop through the range to find the next yellow highlighted cell
    For Each cell In rng
        If found Then
            ' Check for yellow highlighting via conditional formatting
            If cell.DisplayFormat.Interior.Color = RGB(255, 255, 0) Then
                cell.Select
                Exit Sub
            End If
        End If

        ' Check if the current cell matches the start cell
        If cell.Address = startCell.Address Then
            found = True
        End If

        ' Move to the next cell in the range
        If cell.Column = 4 Then
            ' If we are at the last column, move to the next row and start from column A
            Set cell = rng.Cells(cell.Row + 1, 1)
        Else
            ' Otherwise, move to the next column
            Set cell = rng.Cells(cell.Row, cell.Column + 1)
        End If
    Next cell

    ' Inform the user if no yellow highlighted cell was found
    If Not found Then
        MsgBox "No more yellow highlighted cells found in the specified range.", vbInformation
    End If
End Sub

This is amazing... Thank you so much , BigBeachBananas. This solves the problem completely.
I wasn't sure highlights using conditional formatting would work with VBA... this is a masterpiece... If I have ask one last request, is it possible add a function to go back to the first find when the search reaches the bottom of the page?

By the way, here is the link for the cross-reference.

VBA to skip through the conditionally formatted yellow highlighted rows [SOLVED]

Hello, Please note that I cross posted this question to MrExcel (https://www.mrexcel.com/board/threads/vba-to-skip-through-the-conditionally-formatted-yellow-highlighted-rows.1245006/) as I wasn't getting any answers from it. I have a list of data in $, % and texts that are conditionally...

www.excelforum.com