Can I make an Excel 2007 function to only add or to exclude certain highlighted cells?

Question

I have some financial data in Excel 2007 and I highlight certain cells (using the "fill" command), not based on the value, but other factors that only I would recognize.  I want to make a function in another cell that will either return the sum of all highlighted cells within the specified column, or more specifically "return the sum of all cells in the range that are NOT highlighted blue" for example.  Thank you!

garbo7441 · Accepted Answer

Here is one way to do it using VBA.

If your column is not "A", change the "A" references to your column letter, i.e. "F", "L", etc.  If you want the total in a cell other than "B1", change "B1" to the cell reference of your choice.

Copy the code to the clipboard:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i, LastRow, Ttl
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow
If Cells(i, "A").Interior.ColorIndex = xlNone Then
Ttl = Ttl + Cells(i, "A").Value
End If
Next
Range("B1").Value = Ttl
End Sub

Select the appropriate worksheet and right click the sheet tab.

Select 'View Code'.

Paste the code into the editing area to the right.

Close the VBE and return to Excel.

Save the workbook.

Trending News

Can I make an Excel 2007 function to only add or to exclude certain highlighted cells?

1 Answer