write at exceltoexplore@gmail.com : Report Automation|Dashboard in Excel| Provide Excel consulting through macro (VBA) automation |Financial Modeling | Ethical Hacking

Monday, 14 May 2012

Excel : Count Number of Cells Colour in Row

Try below code to count the rowwise number of colour cells.
i have hardcode the column value. even comment (A macro comment is a piece of text in a macro which will not be executed by Excel VBA. It is only there to provide you information about the macro)
the lines which provide the last column

the person from this requirement came she used to count the colour rows manually. in actual file there are around 191 columns day wise & rows contain employee names. its hours of work with this code now in fraction of second.





Sub Mtest()
Dim countcolor As Long, ColorCount As Long
Dim i As Long, LC As Long
'LC = Cells(3, Columns.Count).End(xlToLeft).Column
'change row here
For i = 3 To Sheet1.Cells(Rows.Count, "C").End(xlUp).Row
ColorCount = 0
For Each c In Range(Cells(i, 5), Cells(i, 191)) 'change column count here
'For Each c In Range(Cells(i, 5), Cells(i, LC)) 'change column count here
If c.Interior.ColorIndex = 3 Then ColorCount = ColorCount + 1
Next c
countcolor = ColorCount
Cells(i, 191).Offset(0, 2).Value = countcolor
'Cells(i, LC).Offset(0, 1).Value = countcolor
Next i
End Sub


I have taken Red colour in above example .(Interior.ColorIndex = 3)
Excel Color Palette has an index of 56 colors which can be modified using VBA. Each color in the palette is associated with a unique value in the index that can be changed programatically. At times it is useful to know the relative positioning of the various colors within this index as well as how various versions of Excel treat colors


No comments:

Post a Comment