View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
CraigS CraigS is offline
external usenet poster
 
Posts: 5
Default Pivot Tables Formatting interior color &Type Mismatch

I have created a function to color the interior of cells that contain
percentages (field is "pcnt variance" with 7 different colors from cold
(blue) to hot (red) for my sales application. I'm sure the code could be
better/more efficient but it works ok on defined ranges, however I get a type
mismatch error when I try to apply it to a Pivot table, sometimes, but not
always. It usually blows up when I drag another field down to the rows area.
Is the problem the range? Any help would be appreciated. Below is the code.

Sub colorcells2()

Dim rngsales As Range
Dim i As Integer
Dim j As Integer

Set rngsales = Range("mytable")
For i = 0 To rngsales.Rows.Count - 1
For j = 0 To rngsales.Columns.Count - 1

If ActiveCell.Offset(i, j).Value < -0.04 And ActiveCell.Offset(i,
j).Value -10 Then
ActiveCell.Offset(i, j).Interior.ColorIndex = 31
ElseIf ActiveCell.Offset(i, j).Value < -0.02 And
ActiveCell.Offset(i, j).Value = -0.04 Then
ActiveCell.Offset(i, j).Interior.ColorIndex = 37
ElseIf ActiveCell.Offset(i, j).Value < -0# And
ActiveCell.Offset(i, j).Value = -0.02 Then
ActiveCell.Offset(i, j).Interior.ColorIndex = 35
ElseIf ActiveCell.Offset(i, j).Value 0# And
ActiveCell.Offset(i, j).Value < 0.02 Then
ActiveCell.Offset(i, j).Interior.ColorIndex = xlNone
ElseIf ActiveCell.Offset(i, j).Value = 0.02 And
ActiveCell.Offset(i, j).Value < 0.04 Then
ActiveCell.Offset(i, j).Interior.ColorIndex = 27
ElseIf ActiveCell.Offset(i, j).Value = 0.04 And
ActiveCell.Offset(i, j).Value < 0.08 Then
ActiveCell.Offset(i, j).Interior.ColorIndex = 45
ElseIf ActiveCell.Offset(i, j).Value = 0.08 And
ActiveCell.Offset(i, j).Value < 10 Then
ActiveCell.Offset(i, j).Interior.ColorIndex = 3
Else
ActiveCell.Offset(i, j).Interior.ColorIndex = xlNone
End If
Next
Next

End Sub