Color Function in excel sheet

-
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult

lCol = 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
=ColorFunction($C$1,$A$1:$A$12,TRUE)
Location: New Delhi, Delhi, India

Comments

Post a Comment

Popular posts from this blog

business intelligence reporting tools

-
:- Business Intelligence apparatuses are application virtual products which gather and process a lot of unstructured information from inward and outside frameworks (counting books, diaries, reports, wellbeing records, pictures, documents, email, video, and other solid business sources) for viable basic leadership. BI apparatuses are utilized to distinguish client conduct, enhance perceivability and effectiveness of a business. These instruments likewise help plan information for investigation, for clients to make reports, dashboards, and information perception. The outcomes enable the two workers and directors to quicken and enhance basic leadership, increment operational effectiveness, pinpoint new income possibilities, distinguish advertise patterns, report honest to goodness KPIs and recognize new business openings. SI.No BI Tool Name Link 1 Jaspersoft https://www.jaspersoft.com/ 2 Tableau https://www.tableau.com/ 3 ...
Read more

Adjust The Data Range In Pivot Table(EXCEL)

-
Sub AdjustPivotDataRange() Dim Data_sht As Worksheet Dim Pivot_sht As Worksheet Dim StartPoint As Range Dim DataRange As Range Dim PivotName As String Dim NewRange As String 'Set Variables Equal to Data Sheet and Pivot Sheet   Set Data_sht = ThisWorkbook.Worksheets("Data_Sheet_name")   Set Pivot_sht = ThisWorkbook.Worksheets("Pivot_Sheet_name") 'Enter in Pivot Table Name   PivotName = "National" 'Dynamically Retrieve Range Address of Data   Set StartPoint = Data_sht.Range("A1")   Set DataRange = Data_sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell)) NewRange = Data_sht.Name & "!" & _     DataRange.Address(ReferenceStyle:=xlR1C1)     'Make sure every column in data set has a heading and is not blank (error prevention)   If WorksheetFunction.CountBlank(DataRange.Rows(1)) > 0 Then     MsgBox "One of your data columns has a blank heading." & vbNewLine _       &...
Read more