Cell Color Dependent Function

[adm0104]

I need to make a function (simple excel functions or a VBA macro - either works) that will detect the color of a referenced cell and return a TRUE/FALSE value - FALSE if the cell is colored yellow or red (the red coloring is done by auto-formatting), and TRUE if the cell isn't colored at all.

Some specifics about what I'm trying to do - I work for a company that uses a lot of pumps and we use a GPS tag tracking system to keep track of our pumps. I'm doing some data mining and I have a pretty large spreadsheet that has a column for each pump we have in a certain area. Each row represents a date and the cells contain the GPS coordinates for each pump at each date.

The spreadsheet's cells are filled with either GPS coordinates or with the word "Repair" (signifying that the pump was being repaired and not on the job site). I am trying to get a total of how many pumps were operating on-site on each day. My data sheet already tells me which pumps were being repaired, but it does not tell me which pumps were in transit or simply not in use. To find this out, I have gone through and marked off coordinates that are not at the job site by coloring those cells yellow. I have also marked the cells with the word "Repair" in them, but I used conditional formatting for that. I would like to use a SUMIF function along with the function I mentioned at the start of this post to find the total number of pumps that are being used at each date.

I have attached an example spreadsheet with the desired results. Any cell labelled "GPS" just represents a cell with GPS coordinates in it - I didn't want to waste time making up fake GPS coordinates. The "Pumps on-site" column contains the results I'm after.

Example spreadsheet is here.
pump location example spreadsheet.xlsx

*Update*
Posted the same question on Ozgrid - http://www.ozgrid.com/forum/showthre...602#post669602

Thanks a lot!

[judgeh59]

I'm not sure exactly how and where you want this. but the Object I use is this...

selection.interior.Color

then you can do something like this:

if selection.interior.color = vbRed OR selection.interior.color = vbYellow then
'Set something to True
end if

Remember that colors are actually numbers and using vbRed is a code into that number.....HTH