Count by color

With the built-in COUNTA()-function it's not possible to count cells in a range depending on each cells background color.



With the custom function below you can count the cells depending on their background color:



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

This function is used in the same way as built-in worksheetfunctions. InputRange is the range that the function is going to


count, ColorRange is a reference to a cell containing the background color you wish to count.

E.g. used like this in a worksheet formula: =CountByColor(A1:A100,C1)

WeekStartDate

Function WeekStartDate(intWeek As Integer, intYear As Integer) As Date


Dim FromDate As Date, lngAdd As Long

If intYear < 1 Then intYear = Year(Date) ' the current year

FromDate = DateSerial(intYear, 1, 1)

If Weekday(FromDate, vbMonday) > 4 Then lngAdd = 7

WeekStartDate = FromDate + ((7 * intWeek) - 6) - _

Weekday(FromDate, vbMonday) + lngAdd

End Function

Using Without With-End With Statement

Selection.HorizontalAlignment = xlCenter

Selection.VerticalAlignment = xlCenter

Selection.WrapText = True

Selection.Orientation = 0

Selection.ShrinkToFit = False

Selection.MergeCells = False

Avoiding Excel’s Questions

Avoiding Excel’s Questions:-




Application.DisplayAlerts = False



Use this statement to reinstate the confirmation messages:



Application.DisplayAlerts = True

Speeding Up Your Macros:-




Application.ScreenUpdating = False



Use this statement to reinstate the screen refresh display:



Application.ScreenUpdating = True