• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formatting the Slicer Boxes?

Christof

Member
Hi,

Is it possible to change the colour layout of the Slicer Boxes depending on a value?
Eg, I have 2 brands, and if I choose one brand on the first slicer box, I'd like the slicer boxes to be red layout. Or if they choose the second brand, I'd like them to be purple.

Many thanks
Chris
 

Chihiro

Excel Ninja
Not without VBA.
Using VBA you could change style applied... based on selected value.
Fastest way to do this is to have hidden pivottable with only row labels, housing corresponding field. Link it to slicer.

When slicer item is selected, read the value of first row of the linked pivot.

Use that to apply formatting to slicer.

However, since there are no event to trigger the code upon slicer change, you'll need to use PivotTable Update event to trigger the code.

Something like...
Code:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim slc As SlicerCache
Dim sl As Slicer
Set slc = ThisWorkbook.SlicerCaches("Slicer_Names")
Set sl = slc.Slicers("Names")
With slc
    If .VisibleSlicerItems.Count > 1 Then
        sl.Style = "SlicerStyleLight1"
    Else
        Select Case Sheets("Sheet2").Range("A2").Value
            Case Is = "Name1"
                sl.Style = "SlicerStyleDark2"
            Case Is = "Name2"
                sl.Style = "SlicerStyleDark1"
            Case Else
                sl.Style = "SlicerStyleLight6"
        End Select
    End If
End With

End Sub
See attached sample.
 

Attachments

Top