formula = ADD whatever is not highlighted.

  • Thread starter Thread starter formula = ADD whatever is not highlighted.
  • Start date Start date
F

formula = ADD whatever is not highlighted.

Hi.

I have a workbook which I use as my monthly budget. I
have tried other software made for that, but this is
really what I like.

As transactions go through, I "highlight" the cell, using
a variety of colors. I would like 1 cell (presumably,
one that would calculate, what is left to go through) to
add only the cells that are not highlighted, in a certain
range.

Any and all help is greatly appreciated.
 
These should help.

Sub addnoncolor()
mysum = 0
For Each c In [m1:m5]
If c.Interior.ColorIndex = 2 Then mysum = mysum + 1
Next
MsgBox mysum
End Sub

Sub whatcolor()
For Each c In [m1:m5]
MsgBox c.Interior.ColorIndex
Next
End Sub
 
Excel cannot perform functions on colored cells. Exception to this is if the
cells are colored through Conditional Formatting. Then you could use the same
criteria that colors the cells to calculate.

Check out Chip Pearson's site for User-Defined Functions that can sort, count
and sum by color or exclude colored cells.

Gord Dibben Excel MVP - XL97 SR2 & XL2002
 
Back
Top