Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-14-2019, 09:28 AM
Paul18 Paul18 is offline Add only cells colored yellow Windows 10 Add only cells colored yellow Office 2016
Novice
Add only cells colored yellow
 
Join Date: Feb 2019
Posts: 1
Paul18 is on a distinguished road
Default Add only cells colored yellow


How would you add up a column of numbers whereas you would only add cells in the column having a specific color, i.e cell color of yellow (36)
Reply With Quote
  #2  
Old 02-14-2019, 06:27 PM
Kenneth Hobson Kenneth Hobson is offline Add only cells colored yellow Windows 10 Add only cells colored yellow Office 2016
Advanced Beginner
 
Join Date: Jun 2018
Posts: 37
Kenneth Hobson is on a distinguished road
Default

Welcome to the forum!

Code:
'http://www.mrexcel.com/forum/showthread.php?t=566637
Function SumByColor(CellColor As Range, SumRange As Range)
' SumByColor Function
' VBA and Macros for Microsoft Excel by Bill Jelen Mr.Excel, Page 84
' Modified 07/25/2011 by hiker95
' =SumByColor(J1,A1:G1)
' =SumByColor(cell_address_where_a_specific_color_is, range_to_Sum)
Dim myCell As Range
Dim iCol As Integer
Dim myTotal
iCol = CellColor.Interior.ColorIndex
For Each myCell In SumRange
  If myCell.Interior.ColorIndex = iCol Then
    myTotal = myTotal + myCell.Value
  End If
Next myCell
SumByColor = myTotal
End Function
Reply With Quote
  #3  
Old 02-17-2019, 09:21 AM
alpha alpha is offline Add only cells colored yellow Windows 10 Add only cells colored yellow Office 2010 64bit
Novice
 
Join Date: Jun 2018
Posts: 18
alpha is on a distinguished road
Default

1. You don't need a function SumByColor because you don't want to sum numbers, but to count colors !
2. The function CountByColor that you have, is (for Excel) not based on the color but on the colorindex of the cells. The two colours green you use in your table, don't belong to the 56 colors of the colorindex. (For colorindex, see the helpfile of Excel and/or sheet2 in the attachment; the rownumber = the colorindexnumber of the corresponding cell).
So: either you must use a function that works for all (millions) colors, or you must use a function that only works with the 56 colors of the colorindex. I choosed the first way, so I have modified the function CountByColor in such a way that you can use it for all colors. See the attachment. Hope this helps.
Note: VBA-code can only used in .xlsm-files!
Attached Files
File Type: xlsm MSOfficeForums Paul18.xlsm (17.6 KB, 13 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Add only cells colored yellow Extracting colored cells from one spreadsheet into another Kari Frey Excel Programming 7 09-15-2016 08:48 AM
hi I need total job in month In yellow zEr Excel 2 05-06-2016 03:55 PM
Add only cells colored yellow Populate D1 if A4 is yellow Tuph Excel 3 05-07-2015 02:25 AM
predefine thick border colored lines for highlighted cells dylansmith Excel 0 09-05-2014 07:49 AM
Text Highlighting in Yellow ??? mark4man Publisher 0 12-15-2005 06:46 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:47 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft