Microsoft Excel

How to use VBA to sum Excel values by fill color In One Day

MicroSoft Excel

The article How to effortlessly entirety esteems by a cell’s experience shading in Excel disclosed how to join shading and inherent channels to aggregate qualities by a cell’s fill shading. It’s a simple method to transform hues into significant data. A straightforward separating method won’t generally be satisfactory however, particularly in case you’re reusing the total in different articulations. In the event that you require a remain solitary whole, you can utilize one of two VBA client characterized capacities (UDFs). One depends on a partner section; the other one doesn’t. Neither requires a great deal of code or concentrated information. On the drawback, both have confinements: Neither UDF works with contingent designing or is dynamic. Be that as it may, if this is the thing that you require, you can most likely live with those impediments.

I’m utilizing Excel 2016 (work area) on a Windows 10 64-bit framework, however both UDFs will work in more established renditions. Be that as it may, the program variant doesn’t bolster VBA. You can utilize your own information or download the exhibition .xlsm, .xls, .cls, and .bas documents.

More about Office

Office Q&A: How to prevent Excel’s glue errand from overwriting goal cell’s organization

5 approaches to embed a checkmark into Office reports

Microsoft SharePoint: A guide for business experts (Tech Pro Research)

The most effective method to utilize Excel’s consider the possibility that devices to investigate business situations (free PDF.

Large scale empowered record

  1. Before we start, make sure to spare the exercise manual you’re utilizing as a full scale empowered record as takes after:
  2. Tap the File tab and pick Save As in the left sheet.
  3. Pick Excel Macro-Enabled Workbook (*.xlsm) from the second dropdown.
  4. Snap Save.

The downloadable show record is as of now in this arrangement. In case you’re utilizing a menu adaptation of Excel, this progression isn’t important.

A straightforward UDF and aide segment

In case you’re utilizing direct arranging rather than a restrictive organizing, you can utilize a basic (however constrained) VBA UDF to aggregate qualities by the cell’s fill shading. The example sheet in Figure A demonstrates a couple of direct organizations in segment B: green if the esteem is $1,000 or more prominent and red if the esteem is $200 or less. Presently, in all actuality, you’d presumably utilize contingent organizing in this circumstance, so please pardon the imagined case.

Figure A

excelsumcolor2a.jpg

The immediate organizing in section B recognizes the most noteworthy and least qualities.

The initial step is to include the basic UDF appeared in Listing A to your exercise manual. To do as such, press Alt+F11 to open the Visual Basic Editor (VBE). From the Insert menu, pick Module. At that point, enter ColorSum() physically or import the downloadable .bas document. Try not to reorder from this website page.

Posting A

Capacity ColorSum(CellColor As Range)

‘Total qualities by cell’s experience shading.

ColorSum = CellColor.Interior.ColorIndex

End Function

ColorSum() passes the present cell as a range protest and returns the ColorIndex property setting. Enter the accompanying capacity into D2 and duplicate it to the rest of the cells, as appeared in Figure B:

=ColorSum(B2)

Figure B

excelsumcolor2b.jpg

Call the UDF.

The UDF references the cells in section B and returns the record an incentive for every cell’s fill shading: Green is 48 and red is 3. We couldn’t care less about the cells with no shading relegated (- 4142).

Next, enter the accompanying SUMIF() in E2 and duplicate it to the rest of the cells:

=SUMIF($D$2:$D$18,$D2,$B$2:$B$18)

As should be obvious in Figure C, the SUMIF() work restores the aggregates for the green and red cells: 3,600 and 602, separately. The primary range alludes to the shading file esteems returned by the UDF in segment D. The reference to $D2 utilizes the present record an incentive as criteria—we’re searching for file esteems in D2:D18 that match the list an incentive in D2. The last range reference recognizes the qualities the capacity aggregates when the initial two qualities coordinate.

Figure C

excelsumcolor2c.jpg

SUMIF() restores an aggregate for the green and red cells.

To be perfectly honest, it’s somewhat of a wreck. Despite the fact that it works, you presumably won’t be content with this course of action. Figure D demonstrates a more sensible setup. Physically add the fill hues to G1 and H1; we’ll utilize them as header cells and criteria. At that point, enter the accompanying capacity in G1 and duplicate it to H1:

=SUMIF($D$2:$D$18,ColorSum(G$1),$B$2:$B$18)

The principal go alludes to the list esteems returned by the UDF. The second contention, ColorSum(), restores the shading record an incentive for the header cell; for this situation the cell is G1 and the list esteem is 48. At the point when the qualities for the initial two contentions coordinate, this capacity aggregates the relating esteems in B2:B18. In the event that you would prefer not to see the record esteems in segment D, shroud the section. Therefore, the qualities in section E aren’t fundamental.

Figure D

excelsumcolor2d.jpg

This basic lattice disentangles the summing.

An enhanced UDF

The main UDF relies upon a partner section—the shading file esteems in segment D. You can take out this section utilizing the UDF appeared in Listing B. Figure E demonstrates the consequences of this UDF in the straightforward lattice, yet in push 3. Enter the accompanying capacity into G3 and duplicate it to H3:

=ColorSum2(G$1,$B$2:$B$18)

This UDF utilizes the sentence structure

=COLORSUM2(criteria,range)

where criteria references the shading you’re coordinating and run signifies the qualities you’re summing. More or less, this UDF looks at the fill shade of the present cell (B2:B18) to the fill shading in the criteria cell (G1). At the point when those qualities coordinate, the capacity includes the incentive in the present cell to lSum. In the wake of checking every one of the cells in the passed run (rng), the UDF restores the last estimation of lSum.

Posting B

Capacity ColorSum2(CellColor As Range, rng As Range)

‘Entirety esteems by cell’s experience shading.

Diminish lSum As Long

Diminish iIndex As Integer

Diminish cclr As Variant

iIndex = CellColor.Interior.ColorIndex

Debug.Print iIndex

For Each cclr In rng

In the event that cclr.Interior.ColorIndex = iIndex Then

lSum = WorksheetFunction.Sum(cclr, lSum)

End If

Next cclr

ColorSum2 = lSum

End Function

Figure E

excelsumcolor2e.jpg

This enhanced UDF kills the partner segment.

Dynamic, kind of

Both UDFs work, yet nor is dynamic. Figure F demonstrates the consequence of changing the shade of B3—neither UDF refreshes. Subsequent to evolving, including, or erasing a shading, you should compel the record to recalculate by squeezing Ctrl+Alt+F9. You could set the Volatile property to True in the UDFs, however despite everything you’d need to press F9; that is very little better—approve, it’s no better by any means.

Figure F

excelsumcolor2f.jpg

The UDF doesn’t refresh; it’s a one-time bargain.

There’s no simple method to make either UDF dynamic. Posting C contains an occasion strategy that depends on the Worksheet_SelectionChange occasion. When you select another cell, this strategy powers the document to ascertain when the fill shading changes. Once more, enter the code physically or import the downloadable .cls document. This code goes in the sheet’s module, not the module you included before. (You can pick the sheet in the VBE’s Project Explorer.)

Posting C

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

‘Looks at shading after determination change to constrain calculaton.

‘Powers UDFs, ColorSum() and ColorSum2() to recalculate

‘at the point when fill hues are changed.

Static LastRange As Range

Static LastColorIndex As String

On the off chance that LastRange.Cells.Interior.ColorIndex <> LastColorIndex Then

Application.CalculateFull

End If

Set LastRange = Target

LastColorIndex = Target.Cells.Interior.ColorIndex

End Sub

While it encourages, it is anything but a rich arrangement either. You should make sure to move the choice to trigger it, so it’s as yet risky. I give this last technique to be extensive; it doesn’t generally enhance the circumstance in light of the fact that whichever way you should make sure to drive the recalculation. Moreover, every time you select an alternate cell, you trigger this technique; it will back things off in a bustling sheet. I can’t genuinely suggest its utilization, yet you choose for yourself. Regardless, I incline toward the Ctrl+Alt+F9 keystroke to the worksheet occasion method. I prescribe searching for an approach to join this UDF in a robotized procedure so you can include a recalculation technique, dispensing with the need to make sure to do as such physically.

Stay tuned

Other than their non-unpredictable state, both UDFs have one more colossal constraint—neither works with contingent organizing. In a future article, I’ll furnish a comparative arrangement that works with restrictive designing. I couldn’t care less for these UDFs in light of the fact that they’re not dynamic, but rather I am aware of no other method to get these outcomes utilizing VBA without making a class module that catches arrange changes, and that is well past the extent of a short article.

Get more extraordinary Office tips and traps conveyed to your inbox. Agree to accept TechRepublic’s Microsoft Weekly pamphlet.

Send me your inquiry concerning Office

I answer perusers’ inquiries when I can, however there’s no certification. Try not to send records unless asked for; introductory solicitations for enable that to touch base with appended documents will be erased new. You can send screen captures of your information to help clear up your inquiry. While reaching me, be as particular as would be prudent. For instance, “Please investigate my exercise manual and fix what’s wrong” most likely won’t get a reaction, however “Would you be able to disclose to me why this recipe isn’t restoring the normal outcomes?” may. If it’s not too much trouble say the application and rendition that you’re utilizing. I’m not repaid by TechRepublic for my chance or skill when helping perusers, nor do I request an expense from perusers I help.

Likewise read…

  • Five approaches to exploit Excel list highlights (TechRepublic)
  • Microsoft Office tip: The new AutoSave may not be as helpful as you think (ZDNet)
  • Microsoft Office Certification Training Bundle (TechRepublic Academy)
  • Office Q&A: Two simple approaches to rehash message in a Word record (TechRepublic)

For More Articles Visit : – www.office.com/setup

Leave a Reply

Your email address will not be published. Required fields are marked *