Hi All,

I am building an application where l need to add / delete subtotals to
a range of data on a regular basis and l am doing so using VBA. What l
would also like to do is add some conditional formatting to each of
the total rows. Does anybody have any code to do this or point me in
the right direction ? I assume that when removing the subtotals the
rows are deleted and thus the existing conditional formatting. Is this
correct?

All help gratefully appreciated.

TIA

Regareds

Michael beckinsale

Hi

I never did something like that before, but I guess the macro recorder could
give you the first bit of code, from where to start optimizing/changing it.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music

See http://www.xldynamic.com/source/xld.CF.html#subtotal

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"michael.beckinsale" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi All,
>
> I am building an application where l need to add / delete subtotals to
> a range of data on a regular basis and l am doing so using VBA. What l
> would also like to do is add some conditional formatting to each of
> the total rows. Does anybody have any code to do this or point me in
> the right direction ? I assume that when removing the subtotals the
> rows are deleted and thus the existing conditional formatting. Is this
> correct?
>
> All help gratefully appreciated.
>
> TIA
>
> Regareds
>
> Michael beckinsale
>

Hi Wigi,

Thanks for your response but l have already written the code to
insert / remove subtotals. What l then need to do is automatically
apply conditional formatting to the inserted totals. I could search
the column for partial text (ie *total) and then apply CF to that row/
range but l am hoping for a more efficient / elegant solution.

Regards

Michael Beckinsale

Hi Bob,

Thanks for the link, which shows some useful techniques, but is not
what l am really after. perhaps l did not exlain myself very well.

This is a planning / scheduling application with approx 1000 rows and
100 columns. So to keep the workbook size to a minimum and make the
workbook calculate at an acceptable speed l have kept the use of
formula's (especially nested match / vlookups etc) to a minimum by
using VBA to do 'the donkey work'.

What l really want to do is insert the subtotals using VBA and again
using VBA apply conditional formatting only to the rows which are a
subtotal.

Again l assume that removing subtotals only deletes the subtotal row
and thus the row containing the CF will also be deleted.

Any info / pointers will be really appreciated.

Regards

MB

This should get you started

Sub AddSubtotals()
Dim iLastRow As Long
Dim oCell As Range

With ActiveSheet
.Columns("A:B").RemoveSubtotal
.Columns("A:B").Subtotal GroupBy:=1, _
Function:=xlSum, _
TotalList:=Array(2), _
Replace:=True, _
PageBreaks:=False, _
SummaryBelowData:=True
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set oCell = .Columns(1).Find("Total", LookIn:=xlValues)
If Not oCell Is Nothing Then
Do
oCell.Resize(1, 2).Interior.ColorIndex = 35
Set oCell = oCell.Offset(1, 0).Resize(iLastRow - oCell.Row +
1, 1).Find("*Total*", LookIn:=xlValues)
Loop While Not oCell Is Nothing
End If
End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"michael.beckinsale" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Bob,
>
> Thanks for the link, which shows some useful techniques, but is not
> what l am really after. perhaps l did not exlain myself very well.
>
> This is a planning / scheduling application with approx 1000 rows and
> 100 columns. So to keep the workbook size to a minimum and make the
> workbook calculate at an acceptable speed l have kept the use of
> formula's (especially nested match / vlookups etc) to a minimum by
> using VBA to do 'the donkey work'.
>
> What l really want to do is insert the subtotals using VBA and again
> using VBA apply conditional formatting only to the rows which are a
> subtotal.
>
> Again l assume that removing subtotals only deletes the subtotal row
> and thus the row containing the CF will also be deleted.
>
> Any info / pointers will be really appreciated.
>
> Regards
>
> MB
>

Hi Bob,

Many thanks for the example code. I have not tested it yet but should
this:

Set oCell = .Columns(1).Find("Total", LookIn:=xlValues)

really be this:

Set oCell = .Columns(1).Find(*Total*, LookIn:=xlValues)

ie change " to *

Regards

MB

No it is not necessary. There is another argument to Find, LookAt This can
be one of the following XlLookAt constants: xlWhole or xlPart. You could add
that argument to the Find statements, but I think it defaults anyway,
certainly worked in my tests.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"michael.beckinsale" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Bob,
>
> Many thanks for the example code. I have not tested it yet but should
> this:
>
> Set oCell = .Columns(1).Find("Total", LookIn:=xlValues)
>
> really be this:
>
> Set oCell = .Columns(1).Find(*Total*, LookIn:=xlValues)
>
> ie change " to *
>
> Regards
>
> MB
>

Hi Bob,

As usual you are right. I have tested the code now and it works fine.
Now l need to amend it to cope with the conditional formatting bit.

Thanks for your help

Regards

MB

I already added code to colour the line, seeing as it is VBA, CF seems
superfluous, and it will certainly impact the preformance.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"michael.beckinsale" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Bob,
>
> As usual you are right. I have tested the code now and it works fine.
> Now l need to amend it to cope with the conditional formatting bit.
>
> Thanks for your help
>
> Regards
>
> MB
>