Xây dựng hàm làm việc với màu trong Excel

‘Ham dem so luong theo mau nen chu
Function CountCellsByBackColor(rData As Range, cellRefColor As Range) As Long
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long
cntRes = 0
indRefColor = cellRefColor.CellS(1, 1).Interior.ColorIndex
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.ColorIndex Then
cntRes = cntRes + 1
End If
Next cellCurrent
CountCellsByBackColor = cntRes
End Function’Ham tinh tong gia tri theo mau nen chu
Function SumCellsByBackColor(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes
sumRes = 0
indRefColor = cellRefColor.CellS(1, 1).Interior.ColorIndex
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.ColorIndex Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
Next cellCurrent
SumCellsByBackColor = sumRes
End Function

‘Ham tinh trung binh gia tri theo mau nen chu
Function AverageCellsByBackColor(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes
Dim i As Long
sumRes = 0
indRefColor = cellRefColor.CellS(1, 1).Interior.ColorIndex
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.ColorIndex Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
i = i + 1
End If
Next cellCurrent
AverageCellsByBackColor = sumRes / i
End Function

‘Ham dem so luong theo mau  chu
Function CountCellsByFontColor(rData As Range, cellRefColor As Range) As Long
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long
cntRes = 0
indRefColor = cellRefColor.CellS(1, 1).Font.ColorIndex
For Each cellCurrent In rData
If indRefColor = cellCurrent.Font.ColorIndex Then
cntRes = cntRes + 1
End If
Next cellCurrent
CountCellsByFontColor = cntRes
End Function

‘Ham tinh tong gia tri theo mau chu
Function SumCellsByFontColor(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes
sumRes = 0
indRefColor = cellRefColor.CellS(1, 1).Font.ColorIndex
For Each cellCurrent In rData
If indRefColor = cellCurrent.Font.ColorIndex Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
Next cellCurrent
SumCellsByFontColor = sumRes
End Function

‘Ham tinh trung binh gia tri theo mau chu
Function AverageCellsByFontColor(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes
Dim i As Long
sumRes = 0
indRefColor = cellRefColor.CellS(1, 1).Font.ColorIndex
For Each cellCurrent In rData
If indRefColor = cellCurrent.Font.ColorIndex Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
i = i + 1
End If
Next cellCurrent
AverageCellsByFontColor = sumRes / i
End Function