I am in need of some help in writing a formula please, I think I need the offset function, but Im 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
Chip Pearson has an extensive writeup on using VBA to assess colors.
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)
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
CountColor = dSum
Since 6 is the colorindex for yellow, then you would use this like this:
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
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.