Excel 2003 Pivot and VBA loop through each record to change cellbackground color based on conditions
I am using a pivot table to track grape intake for a winery. Since
Excel 2003 is limited to 3 conditions in the conditional formatting feature, I am trying to write VBA code to: ================================================== == PSEUDO CODE Do After Table Update Select Range of Varname Start Loop Through all Records If PivotItem is cell with data Find Varname of first record Find Sugar of first record If Sugar 0 Case Varname = MERLOT and Sugar < 24 Change background of Varname cell to yellow Case Varname = SYRAH and Sugar < 24 Change background of Varname cell to yellow Case Varname = FRENCH COLOMBARD and Sugar < 23 Change background of Varname cell to yellow Case Varname = CABERNET SAUVIGNON and Sugar < 24 Change background of Varname cell to yellow Case Varname = CHENIN BLANC and Sugar < 20.5 Change background of Varname cell to yellow Case Varname = CARIGNANE and Sugar < 24 Change background of Varname cell to yellow End Case End If End If Loop to next record and continue to do for all records ================================================== == Could the case evaluation also say something like: Case (Varname = MERLOT or Varname = SYRAH or Varname = CABERNET SAUVIGNON_ or Varname = CARIGNANE) and Sugar < 24 ================================================== == Here is the code I have been toying with. It gets a '1004' error. I seem to struggle with selecting the right cell and comparing the cell contents in the program code. Sub FormatPivotCells() Dim ws As Worksheet, pt As PivotTable, pf As PivotField, i As Long, j As Long Dim vn As String, bx As Double Set ws = ActiveSheet If ws.PivotTables.Count < 1 Then MsgBox "No PivotTables on ActiveSheet. Exiting routine.", vbInformation Exit Sub End If Set pt = ws.PivotTables(1) ws.PivotTables(1).PivotFields("Varname").DataRange .Select For i = 1 To pt.PivotFields("Varname").DataRange.Count vn = ws.PivotTables(1).PivotFields("Varname").PivotItem s(i) bx = ws.PivotTables(1).PivotFields("Sugar").PivotItems( i) Next i 'pt.PivotCache.Refresh End Sub ================================================== ========== |
All times are GMT +1. The time now is 10:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com