Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
ubytzika_senzuala din Vrancea
 | Femeie
22 ani
Vrancea
cauta Barbat
22 - 45 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 17 ani
Postari: 2186
|
|
COUNT how many cells have a colored interior (background color) (different from no fill) in UsedRange of ActiveSheet.
Got idea from:
Sub CountColoredCells()
For Each cell In ActiveSheet.UsedRange
If cell.Interior.ColorIndex <> xlNone Then cnt = cnt + 1
Next
MsgBox cnt
End Sub |
_______________________________________
|
|
| pus acum 4 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 739
|
|
COUNT how many cells have colored font:
I put the following code in my PERSONAL.XLSB file.
Public Function CountColor(pRange1 As Range, pRange2 As Range) As Double
'Update by Extendoffice
Application.Volatile
Dim rng As Range
For Each rng In pRange1
If rng.Font.Color = pRange2.Font.Color Then
CountColor = CountColor + 1
End If
Next
End Function |
The syntax of the formula is:
=CountColor(range;cell)
pRange2 will be a cell that has colored text, for example:
in cell A1 put the word BLACK and make font color black
in cell A2 put the word RED and make font color red
in range B1:B500 you have the cells that you want to count
The formula in C1 and dragged down to C2 will be:
=PERSONAL.XLSB!CountColor($B$1:$B$500;A1)
Source:
Modificat de TRaP (acum 4 ani)
|
|
| pus acum 4 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 739
|
|
SUM cells based on Background Color:
SUM cells based on Font Color:
Public Function SumByColor(pRange1 As Range, pRange2 As Range) As Double
'Update by Extendoffice
Application.Volatile
Dim rng As Range
Dim xTotal As Double
xTotal = 0
For Each rng In pRange1
If rng.Font.Color = pRange2.Font.Color Then
xTotal = xTotal + rng.Value
End If
Next
SumByColor = xTotal
End Function |
I put the code above in my PERSONAL.XLSB so my formula will look like this:
=PERSONAL.XLSB!SumByColor(RANGE_TO_SUM;CELL_WITH_COLORED_FONT)
Note:
When you add a new value in the sum range the formula will update automatically, BUT:
When you color an existing value in red in the sum range after the formula was added, you must recalculate the formula for it to calculate - either enter any cell in editing mode, then press enter, or SIMPLY PRESS F9 TO RECALCULATE.
Source:
|
|
| pus acum 3 ani |
|