VBA: Auto sum by cell colour in a row

[Craig_Foggo]

Hi

I'm trying to calculate totals in a row but want to split them out by cell colour (Green and Yellow) where any figures in yellow are totalled and any figures in Green are totalled seperately

Any assistance will be appreciated

Many thanks

Craig

[AliGW]

Sorry - this can only be done with VBA, not formulae. What would make a cell one of these colours? Are they conditionally formatted, and if so, what are the rules? If, and only if this is the case, you'd be able to do this with a formula.

[Craig_Foggo]

The cells would be coloured manually when the information is inputted.

[AliGW]

Then it can't be done without VBA code - sorry. Shall I move your thread to the VBA section?

[Craig_Foggo]

Yes please. I've just had a look at VBA code!!!

Thank you for pointing me in the right direction

[AliGW]

Done.

The only way to do it without VBA would be to have a helper column (or row) to identify the green and yellow numbers with a number or letter.

[nigelog]

Using UDF

Please Login or Register  to view this content.

Use as formula in cell as follows

= SumbyColor(a coloured cell in the range such as "B4", The range containing the coloured cells such as "b1:b36") ie: =SumbyColor(B4,b1:b36)

[torachan]

Try the attached, it works with the Worksheet_Change event.
The area covered is 20 rows x 10 columns, you have to choose your colour while in the cell prior to number entry.
Totaling in columns 11 & 12
I could not see the yellow clearly so at present it is set 'red', the colours are from the 'standard colors' not 'theme'
Note:- the green in the standard bar is not the primary (255) green (MS confuses us once again)
There has to be an easier way as I found the data entry method very frustrating.

[Craig_Foggo]

That is excellent. Thank you. I am trying to put a decimal place in for certain numbers, this is rounded up at present. How can I change that to display the exact figure?

[Craig_Foggo]

Try the attached, it works with the Worksheet_Change event.

Thank you. I'm going by the colour of the cell rather than the text in the call. Thanks for helping though, most appreciated

[Craig_Foggo]

Try the attached, it works with the Worksheet_Change event.

That is excellent. Thank you. I am trying to put a decimal place in for certain numbers, this is rounded up at present. How can I change that to display the exact figure?

[nigelog]

Add function to a standard module and change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[Craig_Foggo]

Add function to a standard module and change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Perfect, Thank you

[nigelog]

Glad to help. Thanks for rep points

[torachan]

Highlight the area (range) and format in normal manner to number of decimal places you require.
I have altered the attached file to fill the cell interior colour (the green is 'light green' the numbers show up better)