Merge Macro - HELP!

[MarkAndrews]

Hi Guys,

I need some help!

I need a macro which will look in column A:A for “Grand Total”, when found, merge the cells of the row which contain “Grand Total” across A:D, then colour the interior of the cells Grey, bold in white and right align

Can anyone please help?

TIA

 

[ADAMC]

Is Grnad total always in the same cell, if so why not use the macro recorder to do all the above...the code can then be tidied up a little?

 

[Joe Was]

First you can get the formmating by Recording a Macro So that part is not important. What we do need is how you want the information itself spaced/formatted, you need to post a sample of what is in A:D now and what you want [Which Cell?] to contain after the macro runs!


In Cell "A:" Grand Total: Cell B, Cell C, Cell D

or what?


Try this as a starting point:


Sub myGrandTot()
'Standard module code, like: Modul1.
Dim lngMyBotR&
Dim objMyRng As Object, Cell As Object

lngMyBotR = ActiveSheet.Range("A65536").End(xlUp).Row
Set objMyRng = ActiveSheet.Range("A1:A" & lngMyBotR)

For Each Cell In objMyRng

If UCase(Left(Cell.Value, 5)) = "GRAND" Then
Cell.Value = Cell.Value & ": " & Cell.Offset(0, 1).Value & ", " & _
Cell.Offset(0, 2).Value & ", " & _
Cell.Offset(0, 3).Value
End If

Next Cell
End Sub

 

[MarkAndrews]

Thanks guys, but i've decided to abandon this