Summarising figures - can anyone help please

  • Thread starter Thread starter ian123
  • Start date Start date
I

ian123

I'd like to create a macro that allows me to do the following but i'm a
bit stuck - can anyone help?

I want to be able to select some numeric cells (a1, a7 a12, a56 for
exampel) and then run a macro which will sum the amounts and place the
total in the cell that is immediately to the right of the last cell
selected in the sum (eg, b56)

However, in addition to this i want all of these cells (a1,a7, a12, a56
and b56) to be highlighted in someway to show that they are linked -
i'm thinking a change of background color or the use of a numbered
key.

Any help on this would be very much appreciated
 
Sub Tester10()
Dim rng As Range, rng1 As Range
Set rng = Selection
Set rng1 = rng.Areas(rng.Areas.Count)
Set rng1 = rng1(rng1.Rows.Count, rng1.Columns.Count)
rng1.Offset(0, 1).Formula = "=Sum(" & rng.Address & ")"
Union(rng, rng1.Offset(0, 1)).Interior.ColorIndex = 5

End Sub

This is sensative to the order you select your cells.
 
Tom,

Thanks very much,that is excellent. However is it possible to include
the option to select which color to highlight the cell or change the
color each time its run.

Basically, it works fine the first time i summarise the data but if i
want to summarise two sets of data within the same range i am left with
two summaries with the same color - which confuses the issue somewhat!

Once again, many thanks - it is very much appreciated
 
Sub Tester10()
Dim rng As Range, rng1 As Range
Set rng = Selection
Set rng1 = rng.Areas(rng.Areas.Count)
Set rng1 = rng1(rng1.Rows.Count, rng1.Columns.Count)
rng1.Offset(0, 1).Formula = "=Sum(" & rng.Address & ")"
rng1.Offset(0, 1).Select
Application.Dialogs(xlDialogPatterns).Show
rng.Interior.ColorIndex = _
rng1.Offset(0, 1).Interior.ColorIndex
End Sub

--
Regards,
Tom Ogilvy


Tom Ogilvy said:
Sub Tester10()
Dim rng As Range, rng1 As Range
Set rng = Selection
Set rng1 = rng.Areas(rng.Areas.Count)
Set rng1 = rng1(rng1.Rows.Count, rng1.Columns.Count)
rng1.Offset(0, 1).Formula = "=Sum(" & rng.Address & ")"
Union(rng, rng1.Offset(0, 1)).Interior.ColorIndex = 5

End Sub

This is sensative to the order you select your cells.
Click to expand...
 
Back
Top