Format Pivot Table Row Where ...
 

I have a fieldset containing the following fields in a pivot table as follows:

Assignments:
StaffName
CustomerName
DateJobIn
DateJobStarted

Can someone show me the code needed to loop through all the rows in the
detailed section of a pivot table and format the color of any row which the
'DateJobStarted' is greater than zero?


--
Rick in NS

Format Pivot Table Row Where ...
 

I would try something like this (untested pseudo code)


Dim rng as Range, rng1 as range
Dim cell as Range, rw as Range
with activesheet.PivotTables(1)
set rng = .Intersect(.rowRange.EntireRow,.TableRange1)
end with
' assume DateJobStarted is in column 4 of the TableRange1
set rng1 = rng.Columns(4).Cells
for each cell in rng1
set rw = cell1.Intersect(cell.EntireRow,rng)
if cell.Value 0 then
rw.Interior.ColorIndex = 3
else
rw.Interior.ColorIndex = xlNone
end if
Next

A lot would depend on how your pivot table is laid out, totals and what you
wanted as a result.

--
Regards,
Tom Ogilvy


"Rick in NS" .(donotspam) wrote in message
...
I have a fieldset containing the following fields in a pivot table as
follows:

Assignments:
StaffName
CustomerName
DateJobIn
DateJobStarted

Can someone show me the code needed to loop through all the rows in the
detailed section of a pivot table and format the color of any row which
the
'DateJobStarted' is greater than zero?


--
Rick in NS

Format Pivot Table Row Where ...
 

Tom:
Thanks for your response. I obviously failed to provide enough detail in my
original post. I have tried to modify your recommendations to enable the
original code in an Access form (on open event) to produce the desired
results with much success. Getting "object not found errors" which does not
surprise me. Can you take a look at the following and suggest what
modifications I should make to incorporate your suggestions correctly? I am
totally inexperienced with vb code related to pivot tables.

=======

On Error GoTo Err_CallPivotTable

Dim pTable As OWC10.PivotTable
Dim pTableView As OWC10.PivotView
Dim rng As Range, rng1 As Range
Dim cell As Range, rw As Range

DoCmd.OpenForm "Schedule Pivot", acFormPivotTable
Set pTable = Forms("Schedule Pivot").PivotTable
Set pTableView = pTable.ActiveView

pTableView.DetailAutoFit = False
pTableView.DetailMaxHeight = 330
pTableView.DetailRowHeight = 15
pTableView.FieldSets("ClientDisplay").Fields("Clie ntDisplay").DetailWidth =
135
DoCmd.Maximize

Exit_CallPivotTable:
Exit Sub

Err_CallPivotTable:
MsgBox Err.Number & " " & Err.Description
Resume Exit_CallPivotTable

======

--
Rick in NS


"Rick in NS" wrote:

I have a fieldset containing the following fields in a pivot table as follows:

Assignments:
StaffName
CustomerName
DateJobIn
DateJobStarted

Can someone show me the code needed to loop through all the rows in the
detailed section of a pivot table and format the color of any row which the
'DateJobStarted' is greater than zero?


--
Rick in NS

Format Pivot Table Row Where ...
 

Looks like your using the controls from the office web components. This
group isn't the correct place for that, although some might be able to help.
I don't use them however.

Look in the microsoft.public.office.developer.web.components perhaps.

--
Regards,
Tom Ogilvy

"Rick in NS" .(donotspam) wrote in message
...
Tom:
Thanks for your response. I obviously failed to provide enough detail in
my
original post. I have tried to modify your recommendations to enable the
original code in an Access form (on open event) to produce the desired
results with much success. Getting "object not found errors" which does
not
surprise me. Can you take a look at the following and suggest what
modifications I should make to incorporate your suggestions correctly? I
am
totally inexperienced with vb code related to pivot tables.

=======

On Error GoTo Err_CallPivotTable

Dim pTable As OWC10.PivotTable
Dim pTableView As OWC10.PivotView
Dim rng As Range, rng1 As Range
Dim cell As Range, rw As Range

DoCmd.OpenForm "Schedule Pivot", acFormPivotTable
Set pTable = Forms("Schedule Pivot").PivotTable
Set pTableView = pTable.ActiveView

pTableView.DetailAutoFit = False
pTableView.DetailMaxHeight = 330
pTableView.DetailRowHeight = 15
pTableView.FieldSets("ClientDisplay").Fields("Clie ntDisplay").DetailWidth
=
135
DoCmd.Maximize

Exit_CallPivotTable:
Exit Sub

Err_CallPivotTable:
MsgBox Err.Number & " " & Err.Description
Resume Exit_CallPivotTable

======

--
Rick in NS


"Rick in NS" wrote:

I have a fieldset containing the following fields in a pivot table as
follows:

Assignments:
StaffName
CustomerName
DateJobIn
DateJobStarted

Can someone show me the code needed to loop through all the rows in the
detailed section of a pivot table and format the color of any row which
the
'DateJobStarted' is greater than zero?


--
Rick in NS