Automatic RGB Color Value Detection RRS feed

  • Question

  • Does anyone know of a way to write VBA code that can sort through a spreadsheet, detect a specific RGB color value of a cell, and classify that cell as being, say, True or False?

    Any thoughts/ideas would be helpful and greatly appreciated.

    A growing developer,


    • Edited by AlSharp13 Friday, February 8, 2013 3:24 PM
    Wednesday, January 30, 2013 2:15 PM

All replies

  • Take look on this procedure. They RGB in use

    Sub Wypelnianie_jasniejsze_RGB()
    'wypełnić kolorem komórkę zaznaczyć obszar, uruchomić
        Dim i As Integer, rng As range
        Dim r As Byte, g As Byte, B As Byte
        Set rng = Selection
        With rng.Cells(1).Interior
        r = .color Mod 256
        g = .color \ 256 Mod 256
        B = .color \ (CLng(256) * 256)
            End With
        For i = 2 To rng.Cells.Count
            With rng.Cells(i).Interior
            .color = RGB(r + (255 - r) * (i - 1) / (rng.Cells.Count - 1), _
                g + (255 - g) * (i - 1) / (rng.Cells.Count - 1), _
                B + (255 - B) * (i - 1) / (rng.Cells.Count - 1))
                End With
            Next i
    End Sub

    But if you want only check color you can use standard interior parameter and use selection case for standard colors.

    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Wednesday, January 30, 2013 3:57 PM
  • Any other theories?? I wasn't able to get this one to work how I needed it to...

    It needs to be able to automatically search through a specific row of a spreadsheet and identify the cells that are a certain color, then take the information from those identified colored cells and move them to another spreadsheet.

    Wednesday, February 13, 2013 1:57 PM
  • Maybe the following should help point a way forward? FindCol is the RGB value of the cell background colour that you seek.

    Dim xlSheet As Worksheet
    Dim iLastRow As Long, iLastCol As Long
    Dim iRow As Long, iCol As Long
    Dim FindCol As Long
        FindCol = RGB(218, 238, 243)
        Set xlSheet = ActiveWorkbook.Sheets(1)
        iLastRow = xlSheet.Range("H" & xlSheet.Rows.Count).End(xlUp).Row
        iLastCol = xlSheet.Cells(1, xlSheet.Columns.Count).End(xlToLeft).Column
        For iRow = 1 To iLastRow
            For iCol = 1 To iLastCol
                If xlSheet.Cells(iRow, iCol).Interior.Color = FindCol Then
                   'The current cell background colour matches.
                   'do what you want with the cell
                End If
            Next iCol
        Next iRow

    Graham Mayor - Word MVP

    Friday, February 15, 2013 10:56 AM