none
How to detect if any cell in a specific row, in any column, is yellow? RRS feed

  • Question

  • I may be thinking about this a little too hard.  It should be pretty straightforward, but I think I'm missing something here.  I want to see if any cell, in one row at a time, is yellow, and if so, perform a task.  I need to scan through all columns in the used range, one cell at a time.

    Set sht = ThisWorkbook.Worksheets("Version Control")
    Lrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row + 1
    
        Set R = ActiveSheet.UsedRange
        For i = 1 To R.Rows.Count
            For j = 1 To R.Columns.Count
                If Cells(i, j).Interior.ColorIndex = 6 Then
                    ' I'm doing some work here . . .
                End If
            Next
            'If Cell is Yellow Then
                finalset = finalset & vbCrLf
            'End If
        Next
    Thanks!

    MY BOOK

    Wednesday, December 21, 2016 10:39 PM

Answers

  • Without looping through each cell in a row:

        Dim rw As Range
        Dim c As Range
        Dim strAddress As String
        Dim f As Boolean
        ' Set up searching for yellow cells
        With Application.FindFormat
            .Clear
            .Interior.Color = vbYellow
        End With
        ' Loop through the rows
        For Each rw In ActiveSheet.UsedRange.Rows
            ' Initialize found flag
            f = False
            With rw
                ' Find first yellow cell
                Set c = .Find(What:="", SearchFormat:=True)
                ' Did we find it?
                If Not c Is Nothing Then
                    ' Set found flag
                    f = True
                    ' Store address
                    strAddress = c.Address
                    Do
                        'Perform an action, e.g.
                        Debug.Print c.Address
                        ' Find next yellow cell
                        Set c = .Find(What:="", After:=c, SearchFormat:=True)
                        If c Is Nothing Then Exit Do
                    Loop Until c.Address = strAddress
                    ' Did we find ANY yellow cell?
                    If f Then
                        ' Action here
                    End If
                End If
            End With
        Next rw
        ' Reset find format
        Application.FindFormat.Clear


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Chenchen LiModerator Thursday, December 22, 2016 7:34 AM
    • Marked as answer by ryguy72 Thursday, December 22, 2016 2:40 PM
    Thursday, December 22, 2016 1:02 AM

All replies

  • Without looping through each cell in a row:

        Dim rw As Range
        Dim c As Range
        Dim strAddress As String
        Dim f As Boolean
        ' Set up searching for yellow cells
        With Application.FindFormat
            .Clear
            .Interior.Color = vbYellow
        End With
        ' Loop through the rows
        For Each rw In ActiveSheet.UsedRange.Rows
            ' Initialize found flag
            f = False
            With rw
                ' Find first yellow cell
                Set c = .Find(What:="", SearchFormat:=True)
                ' Did we find it?
                If Not c Is Nothing Then
                    ' Set found flag
                    f = True
                    ' Store address
                    strAddress = c.Address
                    Do
                        'Perform an action, e.g.
                        Debug.Print c.Address
                        ' Find next yellow cell
                        Set c = .Find(What:="", After:=c, SearchFormat:=True)
                        If c Is Nothing Then Exit Do
                    Loop Until c.Address = strAddress
                    ' Did we find ANY yellow cell?
                    If f Then
                        ' Action here
                    End If
                End If
            End With
        Next rw
        ' Reset find format
        Application.FindFormat.Clear


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Chenchen LiModerator Thursday, December 22, 2016 7:34 AM
    • Marked as answer by ryguy72 Thursday, December 22, 2016 2:40 PM
    Thursday, December 22, 2016 1:02 AM
  • Awesome!  Thanks Hans!!

    Happy Holidays!!


    MY BOOK

    Thursday, December 22, 2016 2:40 PM