1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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?

Discussion in 'Excel Dashboards' started by Christof, Nov 30, 2018.

  1. Christof

    Christof Member

    Messages:
    40
    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
    Khalid NGO likes this.
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,115
    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 (vb):
    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.

    Attached Files:

    Thomas Kuriakose and Khalid NGO like this.
  3. Christof

    Christof Member

    Messages:
    40
    Ah, of course!
    Good thinking :)

Share This Page