Solved: add or subtract depending on format???

[infinity]

Hello all,

I am wondering if there is any way to add or subtract values in a range only if they meet a specific formatting criteria. Example: In cell L3, I would like to subtract the values in range E10:E99 if the format is "bold, red". At the same time I would like to add the values in range F10:F99 if the format is "bold, red". As the formatting changes I want it to reflect the changes in L3. What I am trying to do is keep track of total amounts that meet that formatting criteria. Thank you for any help.

Thanx Scott

[mdmackillop]

Hi Scott,
The following code will add the selected fonts. Changing font colours etc. will not trigger an update, though. You could tie it to a SelectionChange event, which may suffice.
Regards
MD

[VBA]
Function AddFont(Data As Range)
Dim d As Range
For Each d In Data
If d.Font.Bold = True And d.Font.ColorIndex = 3 Then
AddFont = AddFont + d
End If
Next
End Function

[/VBA]

[infinity]

Good Evening MD,

Well it is evening where I am, not where you are. What is a SelectionChange event? Is there a formula that I can put inside cell L3 that will do this? If I am not mistaken, which I may be, won't that trigger an update whenever the formats in the range change? I am really new to this so I can use all the advise that I can get.

Thanx Scott

[XLGibbs]

Hi Infinity,

A SelectionChange event is what it sounds like, but it is referring to an event that the program recognizes when a selection is changed on the worksheet.

A formula will not recognize a format change, nor will it necessarily trigger any such update of other formats unless told to via code ..

However, you may be able to look into conditional formatting, although this would have to be applied judiciously to your range. Up to 3 conditions can be set, relative to values in the cells containing the conditional format..

For example, in Cell C3, if you use ocnditional format you can say If Cell Value = 0 then Format as Bold and Background red. (example).

Format > Conditional Formatting. Refer the the excel help file or post back!

[XLGibbs]

Re-reading your post, a formula solution would be fairly complicated to account for the various possibilities, and conditional formatting will not provide for changing the cells value....however, there are formulas that can evaluate other cells contents to determine the result (IF formula for exampe : If(Condtion, If true result, if false result) is syntax)

Can get kind of complicated though...sample of sheet and before and after samples would be necessary to pose a proper solution....