Excel/formula help

Advertisement


Question
Hi Tom,

I am in need of some help in writing a formula please, I think I need the offset function, but I’m not sure how to write it. I think I also need a custom function that can find a colored cell.
I have some data in Column ”AU12 to AU40”, some of the cells are highlighted yellow, for these cells that are yellow I want to add the cell that is in column “F”. (the cells that are yellow could change)

So for example if “AU12” is yellow and “F12” has number 25 & “AU23” is yellow and “F23” has number 40 & “AU38” is yellow and “F38” has number 15, I would place the answer of 80 in cell “AU46”

I hope I have explained things clearly, if not please let me know.

Thank you in advanced for offering your help.

I am using Excel 2010

Regards
Natalie

Answer
Natalie,

Chip Pearson has an extensive writeup on using VBA to assess colors.  

http://www.cpearson.com/Excel/colors.aspx

It is quite involved and complete but I offer it for your consideration.  It is also assumed that you manually go to the cell and format it to have a background color of 3.  If you are coloring the cell with conditional formatting, than VBA can not easily detect that color.  In fact VBA can not directly detect that color at all.  VBA would need to check the condition that is used by the conditionally formatting.  

A simple VBA custom function that could do what you describe would be

Public Function CountColor(r As Range, c As Long)
 Application.Volatile
 Dim dSum As Double, cell As Range
 dSum = 0
 For Each cell In r
   If cell.Interior.ColorIndex = c Then ' yellow
     If IsNumeric(cell.Parent.Cells(cell.Row, "F").Value) Then
      dSum = dSum + cell.Parent.Cells(cell.Row, "F").Value
     End If
   End If
 Next
CountColor = dSum
End Function

Since 6 is the colorindex for yellow, then you would use this like this:

=CountColor(AU12:AU40, 6)

This function should be put in a general/standard module.  Not in the sheet module.  go to the Visual Basic Editor and do Insert => Module with the workbook as the active project (selected in the project explorer).  Then paste the code in the resulting module.


If the code doesn't appear to work, it is possible that the yellow that you have filled the cells with does not have the colorindex of 6.  You could select one of the colored cells and run this code

Sub TestIndex()
msgbox ActiveCell.Interior.ColorIndex
End Sub

Then the number returned would replace the 6 as the second argument to the function.

The number should be between 0 and 56.  If you get a number like  -4142   that means the cell has no fill.

Note that changing the color of a cell will generally not cause a calculation.  So the function won't update just by changing the color of a cell.  You would need to hit F9 or actually edit the content of a cell to cause a calculation for the function to update.  I made the function volatile so it would always recalculate when the sheet is calculated.  This is about the best you can do.

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.