Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pivot tables for likert-scale type survey items | Excel Worksheet Functions | |||
When refreshing pivot tables my pivot table chart type changes | Excel Discussion (Misc queries) | |||
Help!! Instead of Conditional Formatting, pull interior color from lookup | Excel Programming | |||
Conditional formatting & interior color | Excel Programming | |||
Type Mismatch | Excel Programming |