Adding various cells on a click??

**tholiver** · 08-23-2012, 11:48 AM

Hi All,

I'm having a `vision` (sounds ambitious

) to use a spreadsheet as a quotation tool. What I mean is, imagine a cost of a generic product to which you'd like to add certain options but not all. Then, some sort of trigger is needed to add all highlighted options on a 1 click.

For example, I'd like to highlight 3 out of 6 options and, say, change the background color of these cells to blue, which would automatically trigger a sum function to be placed in a `total` cell. Is this possible? Could anyone help me and work out some magic?

Many thanks in anticipation.

Regards,
Peter.

**patel45** · 08-23-2012, 12:10 PM

assign this code to a button

Public Sub sumcells()
    Dim c As Range
    Dim s
    For Each c In Selection
       If WorksheetFunction.IsNumber(c.Value) Then
         s = s + c.Value
       Else
         s = s & c.Value & " "
       End If
    Next
    Range("G3").Value = s 
    Set c = Nothing
End Sub

**tholiver** · 08-24-2012, 11:02 AM

Dear patel45,

Many thanks for your reply. Unfortunately I can't get it to work. I attached my spreadsheet this time - would you mind having a look and see if you could run this? Your help is greatly appreciated!

**patel45** · 08-25-2012, 05:09 AM

Public Sub sumcells()
    Dim c As Range
    Dim s
    For Each c In Selection
       If WorksheetFunction.IsNumber(c.Value) Then
         s = s + c.Value
         arow = c.Row
       Else
         s = s & c.Value & " "
       End If
    Next
    Range("X" & arow).Value = s
    Set c = Nothing
End Sub

but works only with selected cells, not with blue cells

**tholiver** · 08-25-2012, 10:18 AM

Thanks for getting back to me patel45. Unfortunately I still can't get it to work??? I'm new to VBA in Excel so perhaps I'm missing something here? Would you be able to attach the file that you've managed to get to work?

Please help...

**patel45** · 08-25-2012, 11:57 AM

No problem, find attached

**tholiver** · 08-25-2012, 12:35 PM

Right, could you please help me understand how you've managed to select a couple of different cells and calculate the sum in to cell X12?

**patel45** · 08-25-2012, 02:45 PM

to semplify

Public Sub sumcells()
    Dim c As Range,  s
    For Each c In Selection ' selection is the range of selected cells
         s = s + c.Value ' sum all cells value in selection
         arow = c.Row ' save row
    Next
    Range("X" & arow).Value = s ' display sum
    Set c = Nothing
End Sub

**tholiver** · 08-25-2012, 03:01 PM

I'm getting there...

**tholiver** · 09-14-2012, 01:44 PM

Solved, many thanks for your help patel45!

Adding various cells on a click??

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Adding various cells on a click??

Re: Adding various cells on a click??

Re: Adding various cells on a click??

Re: Adding various cells on a click??

Re: Adding various cells on a click??

Re: Adding various cells on a click??

Re: Adding various cells on a click??

Re: Adding various cells on a click??

Re: Adding various cells on a click??

Re: Adding various cells on a click??

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions