KZNSeagull
Well-known member
So it does! (well F2 and then tab) Well done that man!
Got something to say or just want fewer pesky ads? Join us... 😊
Today's Excel Challenge...
- Thread starter matt
- Start date
So it does! (well F2 and then tab) Well done that man!
TomandJerry
Well-known member
- Oct 1, 2013
- 11,437
Try turning it on and off
KZNSeagull
Well-known member
KZNSeagull
Well-known member
Right @matt:
Here is the function for counting colours, based on the text colour of a particular cell:
Function SumIfColours(MyRange As Range, TestCell As Range) As Double
Dim cell As Range
Application.Volatile True
For Each cell In MyRange
If cell.Font.ColorIndex = TestCell.Font.ColorIndex Then
SumIfColours = SumIfColours + cell
End If
Next cell
End Function
In the empty cell, type =sumifcolours(A1:A6,A2), where A1:A6 is your range and A2 is and example of the text colour you want to test. You then only need one function and to count different colours, just reference a cell with text of that colour. Make sense?
Press F2 and right tab and the cell should update as per @Buzzer.
Here is the function for counting colours, based on the text colour of a particular cell:
Function SumIfColours(MyRange As Range, TestCell As Range) As Double
Dim cell As Range
Application.Volatile True
For Each cell In MyRange
If cell.Font.ColorIndex = TestCell.Font.ColorIndex Then
SumIfColours = SumIfColours + cell
End If
Next cell
End Function
In the empty cell, type =sumifcolours(A1:A6,A2), where A1:A6 is your range and A2 is and example of the text colour you want to test. You then only need one function and to count different colours, just reference a cell with text of that colour. Make sense?
Press F2 and right tab and the cell should update as per @Buzzer.
KZNSeagull
Well-known member
And if you want to sum using the background colour of a cell, just change ...Font.colourIndex to ...Interior.colorindex
This is the stuff of dreams.
Stuff like this makes me chuckle for some reason. I love it.
Official Old Man
Uckfield Seagull
I'm going down a different route here and should point out I use libre office but can program my own accounts software.
I would have three columns for the figures. 1 = output (invoice total) 2 = input (payment made) 3 = balance. Expanding this to include invoice numbers, dates & customer name.
I would have three columns for the figures. 1 = output (invoice total) 2 = input (payment made) 3 = balance. Expanding this to include invoice numbers, dates & customer name.
Thank you so much for this.
KZNSeagull
Well-known member
And here is the function for counting cells with text colours:
Function CountColours(MyRange As Range, TestCell As Range) As Double
Dim cell As Range
Application.Volatile True
For Each cell In MyRange
If cell.Font.ColorIndex = TestCell.Font.ColorIndex Then
CountColours = CountColours + 1
End If
Next cell
End Function
Again, if using background colours rather, change ...Font.colorindex to ...Interior.colorindex
I will now write a function to divert @matt 's salary to my bank account....
Function CountColours(MyRange As Range, TestCell As Range) As Double
Dim cell As Range
Application.Volatile True
For Each cell In MyRange
If cell.Font.ColorIndex = TestCell.Font.ColorIndex Then
CountColours = CountColours + 1
End If
Next cell
End Function
Again, if using background colours rather, change ...Font.colorindex to ...Interior.colorindex
I will now write a function to divert @matt 's salary to my bank account....
Eggmundo
U & I R listening to KAOS
- Jul 8, 2003
- 3,466
What does the '9' do?
It allows you to SUM only the filtered values not the whole population:
http://office.microsoft.com/en-gb/excel-help/subtotal-function-HP010062463.aspx
The reason why it is function_num 9 and not 109 is because filtered numbers are not hidden.