TINTANDSHADE of table rows...

[beckyinDC]

I am stumped on this-

I have a table and want to filter upon a value in a column and then select visible cells and format with a colored background.

I had gotten similar code to work on a different table and expected this would be easy. I can walk through my code and even see the color being applied...but when it hits the tint and shade line, without any error it reverts to a plain white background (at least from visual inspection).

I grabbed the code that I had got working before and plugged that in- same problem- walking through it, the color change seems to take effect but then it hits the tintand shade line and its gone/no error given.

I commented out the tintandshade lines (see code) and the rest of the formatting comands take effect but the colors arent what I wanted...I wanted the tintandshade values.

code below shows the tintandshade commands commented out- I wasnt using the semi-colon syntax at first, but found it was helpful for troubleshooting

the only thing I am wondering is if I could have some formatting set at the table level that is overriding the cell-level formatting- but I dont know if that is possible. thank you very much if you can explain what I must be missing/misunderstanding...

Becky

    ActiveSheet.AutoFilterMode = False
    Range("Z3").Formula = "=SUBTOTAL(3,TableTPRs[TPR '#])" 'this range is deleted at end of this sub
    
    With ActiveSheet.ListObjects("TableTPRs")
        'On Error Resume Next
        
        'Format rows where TPRs are CLOSED
        numfield = .ListColumns("State").Index
        .Range.AutoFilter Field:=numfield, Criteria1:="Closed"
        If Range("Z3").Value > 0 Then
            Range("TableTPRs").Select
            With Selection.Interior
                .PatternColorIndex = xlAutomatic
                .ThemeColor = 5:  End With '.TintAndShade = 0.399975585192419:  End With
        End If
        .AutoFilter.ShowAllData
        
        'Format rows where TPRs are Resolved / Not Closed
        .Range.AutoFilter Field:=numfield, Criteria1:="Resolved / Not Closed"
        If Range("Z3").Value > 0 Then
            Range("TableTPRs").Select
            With Selection.Interior
                .PatternColorIndex = xlAutomatic
                .ThemeColor = 4:  End With '.TintAndShade = 0.799981688894314: End With
        End If
        .AutoFilter.ShowAllData
        
        'Format rows where TPRs are Open
        .Range.AutoFilter Field:=numfield, Criteria1:="Open"
        If Range("Z3").Value > 0 Then
            Range("TableTPRs").Select
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = 0
            End With
        End If
        .AutoFilter.ShowAllData
    End With

 

[Greg Truby]

Dum, dum question, but your .TintAndShade calls were inside the END WITH statements, weren't they?

 

[beckyinDC]

yep. I was deleting and undeleting this

End With '

in the middle of it to toggle back and forth between using and not using the tintandshade command.

BTW- just checked that the original function that works still does...and it does. and I forgot to mention that I am using excel 2007

 

[Greg Truby]

Well, the good news is you're not barking mad. The bad news is, there's something rotten in Denmark here.

I just did some simple testing of this code:

Sub Macro1()
    Dim loTest  As Excel.ListObject
 
    Set loTest = Sheet3.ListObjects("tblTest")
 
    With loTest
        .Range.AutoFilter Field:=1, Criteria1:="red"
        With .ListColumns(3).DataBodyRange.Interior
            .Pattern = xlSolid
            .ThemeColor = xlThemeColorAccent3
            .TintAndShade = 0.4
        End With
        .Range.AutoFilter Field:=1, Criteria1:="blue"
        With .ListColumns(3).DataBodyRange.Interior
            .Pattern = xlSolid
            .ThemeColor = xlThemeColorAccent4
            .TintAndShade = 0.6
            .TintAndShade = -0.4
        End With
        .Range.AutoFilter
    End With
 
End Sub

And it's bombing. As I step through the code it works just fine until hit the second .TintAndShade properties at which time it derails. It looks like it's not a function of being a table because I copied the ws and changed the table (listobject) to a plain old range and the following code fails too:

Sub Macro2()
    Dim rngTest As Excel.Range, _
        rngCol  As Excel.Range
 
    Set rngTest = Sheet5.Range("A1").CurrentRegion
    Set rngCol = rngTest.Columns(3)
    With rngCol
        Set rngCol = .Offset(1).Resize(.Rows.Count - 1)
    End With
 
    With rngTest
 
        .AutoFilter Field:=1, Criteria1:="red"
        With rngCol.Interior
            .Pattern = xlSolid
            .ThemeColor = xlThemeColorAccent3
            .TintAndShade = 0.4
        End With
        .AutoFilter Field:=1, Criteria1:="blue"
        With rngCol.Interior
            .Pattern = xlSolid
            .ThemeColor = xlThemeColorAccent4
            .TintAndShade = 0.6
            .TintAndShade = -0.4
        End With
        .AutoFilter
    End With
 
End Sub

Most frustrating, to be sure. Were it me, before I spent too much time doing this trying to get this approach to work, I'd probably take a crack at using some conditional formatting to see if I could come at this from a completely different direction.

 

[beckyinDC]

Thanks Greg-

sanity confirmation appreciated. I am definitely putting it aside for the day.

If it wasnt working in a nother procedure I would agree to ditch the code.
it keeps bugging me that it is totally reliable elsewhere. grrr...

Also I would need research conditional formatting of table rows based upon particular cells further- I had tried that and didnt getit right earlier, which led me down this path...what do you think- did I skip the easier method for getting the formatting on the tables?

will revisit this and take action tomorrow.

 

[Greg Truby]

If it wasnt working in a nother procedure I would agree to ditch the code.
it keeps bugging me that it is totally reliable elsewhere. grrr...

Then look for subtle differences in code (if there are any). Particularly actions that would "reset" something - perhaps a pointer to a range object? Something. I tried resetting the loTest object variable to no avail. Something is not resetting in the .TintAndShade parent/child relationship.

As for conditional formatting - how dynamic is your data and do you want the coloring to change if the values that drive the coloring change? That's really the key question. If you have a signficant amount of data then you might need to do some performance testing to see if it places a burden on the workbook's calculations.