VBA to skip through the conditionally formatted yellow highlighted rows

Tommy_Oh_Boy

New Member
Joined
Sep 12, 2023
Messages
9
Office Version
  1. 365
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.
 

Attachments

  • Sample Data.JPG
    Sample Data.JPG
    79.6 KB · Views: 9

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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
 
Upvote 0
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,....)?
 
Upvote 0
Row-wise please.
Try this...

VBA Code:
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
 
Upvote 0
Try this...

VBA Code:
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...
 
Upvote 0
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?

VBA Code:
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
 
Upvote 1
Once again cross-posted without links.
Please supply links to any other site where you have asked this question.
 
Upvote 0
How about this?

VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,897
Messages
6,175,271
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top