Create a color vector in VBA

Question

I want to check through multiple years worth of data sheets to find specifically colored cells.

Humans have not consistently chosen the same cell color throughout the years (they may all be the same to the human eye, but have different RGB values).

If I have a cell with the interior color RGB(255,23,50), is there a way to create a color vector to see if a cell's interior color falls on it? I am looking to create a vector with +/- 15 RGB points, so if I am searching for cells with RGB(255,23,50) I want a vector between RGB(255,38,65) and RGB(240,8,35).

I could use an IF statement to see if the color falls between those two values, but I could use a color vector for more applications (and the code would be easier to modify if it needed to be changed).

This if statement works:

If ActiveWorkbook.Worksheets("Sheet1").Range("e5").Interior.Color >= RGB(240, 8, 35) And ActiveWorkbook.Worksheets("Sheet1").Range("e5").Interior.Color <= RGB(255, 38, 65) Then
    MsgBox ("yes")
Else
    MsgBox ("no")
End If

I am looking for something more along the lines of:

dim redVector as long ' or other appropriate variable type

' ***** code that defines the red vector *****

if range("e5").interior.color = redVector then
    ' do stuff
end if

One idea would be to loop from 1 to 255 for each r,g and b, convert them to long and check for the correlation in the value change. — cyboashu
– cyboashu, Commented Jan 17, 2017 at 14:29

cyboashu · Accepted Answer · 2017-01-17 15:00:38Z

1

This should do:

Function IsInVector(srcColor, newColor, lOffset As Long) As Boolean

    Dim lSrcColor As Long
    Dim lNewColor As Long
    Dim lTemp     As Long

    lSrcColor = CLng(srcColor)
    lNewColor = CLng(newColor)

    lTemp = (lSrcColor - lNewColor) / 65536
    lTemp = Abs(Round(lTemp, 0))

    If lOffset <> lTemp Then
        IsInVector = False
    Else
        IsInVector = True
    End If

End Function

'/ Example usage::::  
Sub test()

    Debug.Print IsInVector(RGB(255, 23, 50), RGB(255, 38, 65), 15) '~~~> True
    Debug.Print IsInVector(RGB(255, 23, 50), RGB(255, 43, 63), 15) '~~~> False
    Debug.Print IsInVector(RGB(255, 23, 50), RGB(255, 38, 65), 15) '~~~> True

End Sub

answered Jan 17, 2017 at 15:00

cyboashu

10.5k2 gold badges32 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JonSnow Over a year ago

Thank you @cyboashu. I made a change so that it read If lOffset > lTemp then

Collectives™ on Stack Overflow

Create a color vector in VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related