finnstone
asked on
find out if a cell has yellow fill
i need a formula to test if a cell has been filled with yellow.
any ideas?
any ideas?
The tip to the Range().Interior.Color property is basically correct, here's just some clarification:
The returned value is the value returned by the RGB() function for the respective color, and it consists of the values for Red, Green and Blue in that color. The help for RGB() shows some examples for that, and "classic" Yellow is defined as RGB(255,255,0) or 65535.
Internal it's represented as a 3 Byte integer value, where the right byte (the least significant) represents red, the middle one represents green, and the right one (the most significant) represents blue.
RGB(255,0,0) = 255 = Red
RGB(0,255,0) = 65280 = Green
RGB(0,0,255) = 16711680 = Blue
RGB(0,0,0) = 0 = Black
RGB(255,255,255) = White = 16777215
With that in mind, the minimum length of that number is 1 (for values ranging from 0 (Black) to 8 (an almost invisible faint red).
To check for a yellow cell, the statement would be
The returned value is the value returned by the RGB() function for the respective color, and it consists of the values for Red, Green and Blue in that color. The help for RGB() shows some examples for that, and "classic" Yellow is defined as RGB(255,255,0) or 65535.
Internal it's represented as a 3 Byte integer value, where the right byte (the least significant) represents red, the middle one represents green, and the right one (the most significant) represents blue.
RGB(255,0,0) = 255 = Red
RGB(0,255,0) = 65280 = Green
RGB(0,0,255) = 16711680 = Blue
RGB(0,0,0) = 0 = Black
RGB(255,255,255) = White = 16777215
With that in mind, the minimum length of that number is 1 (for values ranging from 0 (Black) to 8 (an almost invisible faint red).
To check for a yellow cell, the statement would be
if Range(address).Interior.Color = RGB(255,255,0) then (...)
As far as I know it is not possible in standard formulas. You could possibly use a User Defined Function (UDF) which is basically a Visual Basic script that is triggered in the same way as a formula.
Alternatively, looking at it differently, is there a quantifiable reason why a cell would be coloured yellow?
Could that reason for the colour be converted to a Conditional Format so that a cell will automatically go yellow when a condition is met? If that were the case, you could then test for the condition that triggers the format rather than testing for the format itself.
Thanks
Rob H
Alternatively, looking at it differently, is there a quantifiable reason why a cell would be coloured yellow?
Could that reason for the colour be converted to a Conditional Format so that a cell will automatically go yellow when a condition is met? If that were the case, you could then test for the condition that triggers the format rather than testing for the format itself.
Thanks
Rob H
ASKER
what can i type in excel to make any of these work?
The most easy way is a user defined function. Open the VBA editor, insert a new module and paste the following code into:
Use as explained in the attached example workbook.
The function allows a reference to exactly 1 cell and checks if the background is yellow. The result is returned as boolean (true/false). If more than 1 cell is referenced, an Excel error is returned. The function could be used at any place where a boolean value is allowed (i. e. IF(), IIF(), .... )
Hope that helps.
Public Function IsCellYellow(r As Range) As Variant
If r.Cells.Count > 1 Then
IsCellYellow = CVErr(xlErrRef)
Exit Function
End If
IsCellYellow = r.Interior.Color = RGB(255, 255, 0)
End Function
Use as explained in the attached example workbook.
The function allows a reference to exactly 1 cell and checks if the background is yellow. The result is returned as boolean (true/false). If more than 1 cell is referenced, an Excel error is returned. The function could be used at any place where a boolean value is allowed (i. e. IF(), IIF(), .... )
Hope that helps.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you!
Oops - mea maxima culpa ... I've made a quick shot and produced an error ... it takes arguments but still checks for yellow. The function should read
( Just 1 line changed, the final test ...)
And here's the promised example workbook, too: Example.xlsm
Public Function IsCellColor(r As Range, Red As Integer, Green As Integer, Blue As Integer) As Variant
If r.Cells.Count > 1 Then
IsCellYellow = CVErr(xlErrRef)
Exit Function
End If
If (Red < 0) Or (Red > 255) _
Or (Green < 0) Or (Green > 255) _
Or (Blue < 0) Or (Blue > 255) Then
IsCellYellow = CVErr(xlErrValue)
Exit Function
End If
IsCellYellow = r.Interior.Color = RGB(Red, Green, Blue)
End Function
( Just 1 line changed, the final test ...)
And here's the promised example workbook, too: Example.xlsm
?Range("A1").interior.colo
That will return a 6 to 8 digit number and you can use that number when looking at other cells.