You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- formula help

Advertisement

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

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

- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 10 | Politeness = 10 |

Comment | Thank you so much Tom, this is exactly what I am after, you explained things very clearly. Regards Natalie W |

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

Answers by Expert:

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.]

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.