Automatically add Conditional formatting on data/subtotals

[michael.beckinsale]

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

 

[Wigi]

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.

 

[Bob Phillips]

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]

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

 

[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

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

You need to put in the xlpart command specifically. It does not default to
xlpart, but retains what the last setting was. It you want it to always
work, you need to state specifically what you want.

 

[michael.beckinsale]

Hi Bob,

Performance is my main worry.

The CF is carried across the subtotal row for 90 columns and is used
in this case to highlight 3 conditions:

1) Fully billable day
2) Partially billable day
3) Non billable day

I estimate ther will be approx 100 rows on which CF needs to be
applied. The current development workbook has 61 rows on which the CF
was applied manually and the calculation speed is fine. However
applying CF by code & looping will probably be pretty slow.

Do you think it might be quicker / more efficient to apply CF to 1
subtotal row and then apply it to the others using FormatPainter?

Do you have any alternative idea's?

Regards

MB

 

[Bob Phillips]

It is not the applying of CF that is the issue, but the performance impact
thereafter. CF is always evaluated when Excel recalculates, regardless of
whether it relates to changed cells. But for 100 rows it should not be
relevant. Finding as I showed, and applying CF then should be fine and quick
enough.

--
HTH

Bob

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