mbake16
05-19-2010, 08:58 AM
I've set up a simple pivot table with two cells in which the user can type an item description which Bolds the entire pivot table row of any cell matching the description entered. My macro ("PivotMacro") sets up these conditional format rules for the pivot table.
Sticking point: the macro applies the Bold format to only ONE of the formatting rules, even though I've written code to apply it to BOTH rules.
Any ideas on how to have the Bold formatting apply to both rules? An example file is attached for reference. Run the "PivotMacro" sub to see the results I'm referring to.
Sub PivotMacro()
Dim cell As Range
Dim pivotcolumns As Range
Set pivotcolumns = Range("h15:j15")
'Bold entire pivot table row of cell in column G matching item description entered in cell "H5"
For Each cell In pivotcolumns
cell.Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF($h$5<>"""",SEARCH($h$5,$g15),FALSE)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Font
.Bold = True
End With
Selection.FormatConditions(1).StopIfTrue = True
Selection.FormatConditions(1).ScopeType = xlDataFieldScope
Next cell
'Bold entire pivot table row of cell in column G matching item description entered in cell "H6"
For Each cell In pivotcolumns
cell.Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF($h$6<>"""",SEARCH($h$6,$g15),FALSE)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Font
.Bold = True
End With
Selection.FormatConditions(1).StopIfTrue = True
Selection.FormatConditions(1).ScopeType = xlDataFieldScope
Next cell
End Sub
Sticking point: the macro applies the Bold format to only ONE of the formatting rules, even though I've written code to apply it to BOTH rules.
Any ideas on how to have the Bold formatting apply to both rules? An example file is attached for reference. Run the "PivotMacro" sub to see the results I'm referring to.
Sub PivotMacro()
Dim cell As Range
Dim pivotcolumns As Range
Set pivotcolumns = Range("h15:j15")
'Bold entire pivot table row of cell in column G matching item description entered in cell "H5"
For Each cell In pivotcolumns
cell.Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF($h$5<>"""",SEARCH($h$5,$g15),FALSE)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Font
.Bold = True
End With
Selection.FormatConditions(1).StopIfTrue = True
Selection.FormatConditions(1).ScopeType = xlDataFieldScope
Next cell
'Bold entire pivot table row of cell in column G matching item description entered in cell "H6"
For Each cell In pivotcolumns
cell.Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF($h$6<>"""",SEARCH($h$6,$g15),FALSE)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Font
.Bold = True
End With
Selection.FormatConditions(1).StopIfTrue = True
Selection.FormatConditions(1).ScopeType = xlDataFieldScope
Next cell
End Sub