Solved

find out if a cell has yellow fill

Posted on 2016-11-01
8
50 Views
Last Modified: 2016-11-03
i need a formula to test if a cell has been filled with yellow.

any ideas?
0
Comment
Question by:finnstone
8 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41869279
In the Immediate window of Visual Basic, type

?Range("A1").interior.color ' Change A1to the address of a yellow cell

That will return a 6 to 8 digit number and you can use that number when looking at other cells.
0
 
LVL 14

Expert Comment

by:frankhelk
ID: 41869979
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
if Range(address).Interior.Color = RGB(255,255,0) then (...)

Open in new window

0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41870104
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
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

Author Comment

by:finnstone
ID: 41872045
what can i type in excel to make any of these work?
0
 
LVL 14

Expert Comment

by:frankhelk
ID: 41872095
The most easy way is a user defined function. Open the VBA editor, insert a new module and paste the following code into:

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

Open in new window


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.
0
 
LVL 14

Accepted Solution

by:
frankhelk earned 500 total points
ID: 41872104
P.S.: To provide more amusement to the crowd, here's the luxury version of the function, which allows to check for any desired color:

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(255, 255, 0)

End Function

Open in new window


Should be mostly self-explaining ...  ;-)
0
 

Author Comment

by:finnstone
ID: 41872108
thank you!
0
 
LVL 14

Expert Comment

by:frankhelk
ID: 41872119
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

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

Open in new window


( Just 1 line changed, the final test ...)

And here's the promised example workbook, too: Example.xlsm
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now