Adds al the black font value in column but not the blue font values??
Any ideas guys? Thanks.
|Check out our Excel VBA Resources|
Application.ScreenUpdating = False
Do Until ActiveCell.Font.ColorIndex = xlAutomatic
If ActiveCell.Font.ColorIndex = 1 Then
ActiveCell.Offset(0, 1).Value = ActiveCell.Value
ActiveCell.Offset(0, 1).Formula = "=SUM(R2C2:R[-1]C)"
Application.ScreenUpdating = True
Good luck, hope this helps.
Function SumBlk(rng As range)
Dim cell As range, x As Integer, tot As Double
x = 0
For Each cell In rng
If IsNumeric(cell) Then
If cell.Font.ColorIndex = 1 Then
If x > 0 Then
tot = tot + cell.Value
tot = cell.Value
x = 1
SumBlk = tot
Whether this works depends how the colour of the cell is set. If, for example, the cell is red because it is a negative number and the number format is set to show negative numbers in red this won't work.
Another problem is forcing the function to recalculate when someone changes a cell's colour. Even Application.Volatile won't necessarily work in this case as that only recalculates when the worksheet gets recalculated. Changing a cells colour doesn't cause this.
Function SumColour(rngSum As Range, varColour As Variant) As Variant
Dim rngCell As Range
For Each rngCell In rngSum.Cells
If rngCell.Font.ColorIndex = varColour Then
SumColour = SumColour + rngCell.Value
Works fine, i understood the dangers, but i alone use my sheets and it a guild not hard fast data reports, so my maintance will be perfact an your code will do just as io need, best i find a font cart.
BTW I use blue for red as reds taken as you point out also Blues easier to see i find, just a tip inthe finance world.
Cheers all ive lots to go on a big thanks, now all i have to do is play and get all trhe font colours listed for reference