Count the Number of Colored Cells in Excel

First open the workbook in which you wish to count or sum cells by a fill color. Now go into Visual Basic. Insert new module:

Code:

Funtion ColorCount(rColor As Range, rRange as Range, Optional Sum As Boolean)
Dim rCell as Range
Dim lCol as Range
Dim vResult


1Col = rColor.Interior.ColorIndex

If Sum = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.Sum(rCell, vResult)
End If
Next rCell

Else

For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell

End If

COlorFunction = vResult

End Function

--------------------------------------------------------

You can use the function that was just created by inputing this code into a cell

=ColorFunction($C$1,$A$1:$A$12,TRUE) to sum the values in the A:A column Range that have the same fill color as cell $C$1. The reason it will sum in because the code will have True as the last argument for the custom function.

VBA Code for Random Generation of Stats

CoinToss()

Dim x As Integer
Dim Row as Integer
Dim NumberOfFunds As Integer

erOfFunds = Sheets("Sheet1").Range("NumberOfPeople")

Worksheets("Sheet1").Range("ClearNumber").Select
Selection.ClearContents----------------------------Clears data from cells

For x = 1 To NumberOfFunds-------------Starts loop

Application.ScreenUpdating = False--------------Stops flickering of screen

Calculate-------------------------------This calculates the random generation function
Range("Future1").Select
Selection.copy
Range("Option1").Offset(x, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkpBlanks _
:=False, Transpose:=False

Range("Future2").Select
Selection.copy
Range("Option2").Offset(x, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Next

Range("M10:N109").Select----------------------This formats the numbers that are generated
Application.CutCopyMode = False
Selection.NumberFormat = "0.0000"
Selection.NumberFormat = "0.000"
Selection.NumberFormat = "0.00"
Selection.NumberFormat = "0.0"
Selection.NumberFormat = "0"

Range("A1").Select------------------ends macro in cell a1

Sub

Find Function in VBA

----------This Search Feature allows you to add a variable such as strMyString------------
----------strMyString can be any value you want to search for in a worksheet-------------

Dim strMyString as String----------Sets the variable for use

Cells.Find(What:=strMyString, After:=Activecell, Lookin:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

Basic Setup for VBA Code:

Sub Data_Filter()

Dim x as Integer-------------These are Variables
Dim Y as String--------------These are Variables
Application.ScreenUpdating = False-----------This Stops the screen from Flickering
On Error GoTo EndOfSection-------------This keeps your macro running when there is a error

For x = 1 to NumberofTrainings-------Start of a loop
Sheets("Sheet1").Select------------Selecting a Worksheet
Range("C2").Offset(x, 0).Select----------Selecting a range and then moving from that range
Y = Activecell------------------Telling a Variable what its value is
Sheets.Add.Name = NameofTab------------Creating a new worksheet
Range (ActiveCell.EntireRow.Address) (1, 1).Select----------Moving activecell to the beginning of a row

Next------------------------End of a loop

EndOfSection:-------------Where code breaks to when a error occurs

End Sub---------------Ends the macro

Text Functions (II)
FIND(find_text;within_text;start_num)
It searchs for a text inside another text and returns the position of the text searched. It performs the search reading the text from left to right, from the initial position indicated in numb_initial. In contrast with the function SEARCH, FIND does not distinguish between capital and small letters and admits joker characters (? just one character, * any number of characters, scape character).
Example: =FIND("Buy";"Watching the sea, an intense emotion takes hold of me...";1) returns 12
=FIND("W?r";"Watching the sea, an intense emotion takes hold of me...";1) returns 1
EXACT(tex1;tex2) Function
It returns a logical value (true/false) depending on whether both text chains are the same or not.
Example: =EXACT(" this is the same?";" is this the same?") returns FALSE
It is normally used to compare the values stored in two cells.
LEFT(tex;num_chars) Function
It returns the number of characters specified from the beginning of the text chain.
Example: =LEFT("The sun can not compete with the brightness of your gaze";6) returns "The sun"

LEN(text) Function
It returns the number of characters that the text chain has, that is its length.
Example: =LEN("The sun can not compete with the brighteness of your gaze") returns 51

CLEAN(text) Function
It gets rid of character that can not be printed.
Example:If you type in a cell:=CHARACTER(7)&"This text is valid"&CHARACTER(7) in the cell you would see this: if you type =CLEAN(CHARACTER(7)&"This text is valid"&CHARACTER(7)), the characters that can not be printed will disappear and you will see:

UPPER(text) Function
Converts into capital letters a text chain.
Example: =UPPER("converts to capital") returns "CONVERTS TO CAPITAL"

LOWER(text) Function
Converts to small letters a text chain.
Example: =LOWER("COME ON NOW SMALL LETTERS") returns "come on now small letters"

DOLLAR(number;decimals)
It converts to text a number using currency format.
Example: =DOLLAR(25;2) returns "25,00 € "

PROPER(text) Function
It converts the first letter of each word of a text into capital letters, the rest of the word into small letters.
Example: =PROPER("antonio manuel ramiro") returns "Antonio Manuel Ramiro"

REPLACE(old_text;start_num;num_chars;new_text) Function
It replaces part of a text chain for another.
Example: =REPLACE("If this is the original text, it will be modified";21;8;"Into this") returns "If this is the text Into this, will be modified"

REPT(text;num_times)
It repeats a text a previuosly fixed number of times.
Example: =REPT("You repeat yourself";5) returns "You repeat yourself You repeat yourself You repeat yourself You repeat yourself You repeat yourself"

SUBSTITUTE(text;old_text;new_text;instance_num) function
Substitutes in the text, an original text_ for new text_.
Example: =SUBSTITUTE("The price for the whole project implies..."; "price"; "cost") returns "The cost for the whole project implies..."

T(value) function
It checks whether the value is text and returns text if it is or double inverted comas if it isn't. With this function you can eliminate the values in a cell that aren't text.
Example: =T("This is text") returns "This is text"

TEXT(value;format_text) function
It converts a value in text.
Example: =TEXT(25;"0,00 €") returns "25,00 €"

BAHTTEXT(number)
Converts a number in thai (Baht) text.
The baht format can be changed into a different style used. Regional configuration or Regional options in the Windows Control panel .
Example: =BAHTTEXT(25) returns figure 25 in letter but in Thai.

VALUE(text) function
Converts a text that represents a figure in a figure.
Example: =VALUE("254") returns 254 numerical format.

Lesson 1 - List of common functions and definitions

When inserting a function into a cell always start with a "=" sign.
for example if you want to add two cells together you would write:

=sum(a1:b1) or
=a1+b1

Here is a list of excel function that I find my self using most often.

=Sum()
Use this function when adding multiple cells in a row or column
example: add all the data found in cell b1, b2 and b3
=Sum(B1:B3)

=IF()
Use this function when trying to answer simple yes or no questions
example: If cell b8 is greater than 1 then give me "apple" if not give me "peach"
=IF(B8>1,"apple","peach")

=SumIF()
Use this function when trying to add cells together with a certain characteristic
example: If "apple" is in Column A then add all the values from Column B that
have "apple" in the Column A rows
=Sumif(A:A,"apple",B:B)

=Counta()
Use this function when counting the number of cells with any value in them.
example: I want to know the number of cells that are not empty in column A
=Counta(A:A)

=CountIF()
Use this function when counting the number of cells with a certain characteristic
example: I want to now the number of cells with apple in the text in column A
=countif(A:A,"apple")

=Average()
=Mid()
=Left()
=Right()
=PMT()
=FV()
=PV()
=Nper()
=Rate()

Excel for beginners

Excel can be a very frustrating program until you start to understand how things work. Just by learning a few principles, one can learn how to read, write and program in excel.

Principle:
1. When in doubt google your question. Microsoft has a horrible user manual.
2. Excel Works with columns and rows.
3. Keep procedures simple and direct.
4. When building models in excel try to build one piece at a time.
5. Always start a formula with an equals sign.

This blog is meant to be a learning resource for beginners and experts. If you are confused please leave a comment.