Posts by cheb_michael

Re: Sum Conditional Formatted Cells By Color

Cheers guys.
Thanks for your help.

Re: Sum Conditional Formatted Cells By Color

Argh I don't understand the problem.

Just to double check:

You go into the VBA section (Alt + F11)
Insert \ Module.

Copy paste your Code.

Save

Go back in the Spreadsheet and unto the cell I want the sum done and write
" =SumIfColourAdd(J14:J27,J14)"

If that's correct it doesn't work :([hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Alright guys,

Regarding this saga I found a possible way around.

I found on a website a code which tells you the colour code used (blue is 33, orange is 45, green is 4) in the cells which have been formatted. http://vbaexpress.com/kb/getarticle.php?kb_id=190 . This works for Excel 2000 btw.

I then did a SUMIF function to sum a range of cells if it's corresponding colour code was of a certain value.
=SUMIF(K14:K27,33,J14:J27)

It does the trick but I had to insert a new column and hide the data.

I'm just disapointed that we couldn't find a solution which would bypass what I've just described. As Pike mentionned, it might just be my version of Excel 2000 which doesn't allow his code to work.

Anyways gents, have a good day, I'll keep an eye out to see if anyone finds a solution for Excel 2000.

Thanks for your help.

Michael D

Re: Sum Conditional Formatted Cells By Color

Pike,

It doesn't seem to work, it sums up all the cells regardless of their colour.

Is there a way of defining VBA to pick the colour of the condition in the line:
rColourCell.Interior.Color
maybe instead of "rColourCell.interior" we can have something for condional formatting.

I'm really crap at VBA so I'm really just throwing ideas in the air.

Michael D

Re: Sum Conditional Formatted Cells By Color

Here you go Gents,

It should be self-explanatory:
Depending on the name it's either a Project manager, field service or tech service engineer (drop down menu).
As you see the total column sums up all the cost relevant to the specific name and his role and changes colour depending on his role.

I want the top three cells (Grand Total) to sum up all the smaller totals depending on their cell colour which is dependant of the conditional formatting.

I hope this helps.

Thanks for taking the time to help me.

Regards

Michael D

forum.ozgrid.com/index.php?attachment/29388/

Re: Sum Conditional Formatted Cells By Color

Dave,

I've tried the SUMIF and DSUM but my criteria is a conditional format which will return one of three colours. i.e I want to pick all the cells which have turned blue because of my previous conditions and sum them into another cell.
I don't see how I can fit the conditional formatting into the criteria.
Is it maybe worth me attaching the spreadsheet im working on to make it clearer?

regards

Michael

Re: Sum Cells By Color From Conditional Formatting

Hello Dave,

You'll have to excuse my ignorance but how would you fit that into the VBA code or even in the formula of the cell I want the sum?

Tanks,

Michael D

Hello Everyone,

I'm trying to use the SUMCOLOR function found on your website to sum cells which have conditional formatting (background colors with bold writing) applied to them, but it doesn't work.

I'm attaching the code found on the website as a reference.
I read some content on Cpearson but it's way beyond my understanding.

Function SumColor(rColor As Range, rSumRange As Range)






''''''''''''''''''''''''''''''''''''''


'Written by Ozgrid Business Applications


'www.ozgrid.com






'Sums cells based on a specified fill color.


'''''''''''''''''''''''''''''''''''''''


	Dim rCell As Range


	Dim iCol As Integer


	Dim vResult






	iCol = rColor.Interior.ColorIndex






		For Each rCell In rSumRange


			If rCell.Interior.ColorIndex = iCol Then


				vResult = WorksheetFunction.Sum(rCell) + vResult


			End If


		Next rCell






	SumColor = vResult


End Function

Thank you,

Michael D