Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Count by color using VBA in Microsoft Excel
I am having the following formula to count on the basis of colour. For example
Total 12 cell out of which Red - 1, Yellow - 5 & Green - 6 so by using this formula this should give proper count as mentioned above. I need you assistance as i have never used macro using VBA in Microsoft Excel. Could you please guide me (Step by Step) how to run this macro in excel. Appreciate your kind assistance. Thanks in advance. Kamlesh Function CountByColor(InputRange As Range, ColorRange as Range) As Long Dim cl As Range, TempCount As Long, ColorIndex As Integer ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex TempCount = 0 For Each cl In InputRange.Cells If cl.Interior.ColorIndex = ColorIndex Then TempCount = TempCount + 1 End If Next cl Set cl = Nothing CountByColor = TempCount End Function |
#2
|
|||
|
|||
Count by color using VBA in Microsoft Excel
Hi Kam
Copy the code to the clipboard. In Excel press Alt+F11. This will open the Visual Basic Editor (VBE). From the menus select InsertModule. Paste the code into the new module (major white part on right of screen). Click on the Excel icon (Left most button on toolbar) to return to Excel. In the cell that you want your result type the formula: =countbycolor(D4:D16,D4) Where D4:D16 is the range of cells you want to check and D4 is a single cell which is set to the colour you are wanting to count. This does not need to be in the first range. Hope this helps Rowan Kam wrote: I am having the following formula to count on the basis of colour. For example Total 12 cell out of which Red - 1, Yellow - 5 & Green - 6 so by using this formula this should give proper count as mentioned above. I need you assistance as i have never used macro using VBA in Microsoft Excel. Could you please guide me (Step by Step) how to run this macro in excel. Appreciate your kind assistance. Thanks in advance. Kamlesh Function CountByColor(InputRange As Range, ColorRange as Range) As Long Dim cl As Range, TempCount As Long, ColorIndex As Integer ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex TempCount = 0 For Each cl In InputRange.Cells If cl.Interior.ColorIndex = ColorIndex Then TempCount = TempCount + 1 End If Next cl Set cl = Nothing CountByColor = TempCount End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Export to Microsoft Excel from IE | Excel Discussion (Misc queries) | |||
import payees from Excel into Microsoft Money 2006 | Excel Discussion (Misc queries) | |||
Excell VS Office | Excel Discussion (Misc queries) | |||
Microsoft Access Report into Excel Spreadsheet | Excel Discussion (Misc queries) | |||
How can I use count function in excel where I have several criter. | Excel Worksheet Functions |