none
Column/Row Search RRS feed

  • Question

  • Ok, I wrote out code to look through a spreadsheet that will find columns of a specific RGB color of green and print the column number. How can I look through ONLY the green columns that were found to find cells that are another specific color??

    Friday, March 8, 2013 3:22 PM

Answers

  • Something like:

    Sub luxation()
    Dim arrcritNO(1 To 9999) As Long
    icols = 999
    icritcols = 1
    For icnt = 1 To icols
        If Cells(1, icnt).Interior.Color = RGB(146, 208, 80) Then
            arrcritNO(icritcols) = icnt
            icritcols = icritcols + 1
        End If
    Next
    icritcols = icritcols - 1
    For L = 1 To icritcols
        For LL = 1 To 9999
            If Cells(LL, arrcritNO(L)).Interior.Color = RGB(20, 100, 200) Then
                MsgBox Cells(LL, arrcritNO(L)).Address
            End If
        Next LL
    Next L
    End Sub

    The L-loop go across only the "special columns"

    The LL-loop goes down each of those columns.


    gsnu201301

    • Marked as answer by AlSharp13 Friday, March 8, 2013 5:22 PM
    Friday, March 8, 2013 5:05 PM
    Moderator

All replies

  • Post your current code.

    gsnu201301

    Friday, March 8, 2013 3:50 PM
    Moderator
  • This is the code I currently have that looks through the first row to find green columns and it displays the location and value.
    
    
    For icnt = 1 To icols
     If Cells(1, icnt).Interior.Color = RGB(146, 208, 80) Then
                    icritcols = icritcols + 1
                    arrcritNO(icritcols) = icnt
                    arrcritName(icritcols) = Cells(1, icnt).Value
                
                    Debug.Print "Col#" & icnt & " " & Cells(1, icnt).Value & " arrno=" & icritcols
                    
                 End If



    
    Friday, March 8, 2013 4:18 PM
  • Something like:

    Sub luxation()
    Dim arrcritNO(1 To 9999) As Long
    icols = 999
    icritcols = 1
    For icnt = 1 To icols
        If Cells(1, icnt).Interior.Color = RGB(146, 208, 80) Then
            arrcritNO(icritcols) = icnt
            icritcols = icritcols + 1
        End If
    Next
    icritcols = icritcols - 1
    For L = 1 To icritcols
        For LL = 1 To 9999
            If Cells(LL, arrcritNO(L)).Interior.Color = RGB(20, 100, 200) Then
                MsgBox Cells(LL, arrcritNO(L)).Address
            End If
        Next LL
    Next L
    End Sub

    The L-loop go across only the "special columns"

    The LL-loop goes down each of those columns.


    gsnu201301

    • Marked as answer by AlSharp13 Friday, March 8, 2013 5:22 PM
    Friday, March 8, 2013 5:05 PM
    Moderator
  • Thanks so much, you're awesome!
    Friday, March 8, 2013 5:22 PM