Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts

    Sparklines: Conditional formatting?

    Is it possible to colour the sparklines based on a value in another column (e.g. C - When positive: Green, Negative: Red, Otherwise: Neutral blue)?

    Sparklines.png

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Not directly but you can fake it with some additional tables and sparklines and the camera tool. See attached.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi rvWoody

    ..another way would be to detect a changed value in your range [C2:C3], and then have the corresponding sparkline colour set.
    You would need to use the sheet event code:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, [c2:c4]) Is Nothing Then
    setSparkColour
    End If
    
    End Sub
    ..together with code like this (in a general Module):
    Code:
    Sub setSparkColour()
    
    For Each cell In [B2:B4]
    Select Case cell.Offset(0, 1)
    Case Is > 0
    cell.SparklineGroups.Item(1).SeriesColor.Color = vbGreen
    Case Is < 0
    cell.SparklineGroups.Item(1).SeriesColor.Color = vbRed
    Case Else
    cell.SparklineGroups.Item(1).SeriesColor.Color = vbBlue
    End Select
    Next
    
    End Sub
    see attached example

    zeddy
    Attached Files Attached Files

  4. The Following User Says Thank You to zeddy For This Useful Post:

    rvWoody (2015-05-16)

  5. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi rvWoody

    ..and if you wanted to choose your own colour, instead of using vbGreen, vbRed, vbBlue etc you could directly specify the RGB colour, or, if you wanted to use Excel2010 named values, you could use any of these:

    rgbAliceBlue
    rgbAntiqueWhite
    rgbAqua
    rgbAquamarine
    rgbAzure
    rgbBeige
    rgbBisque
    rgbBlack
    rgbBlanchedAlmond
    rgbBlue
    rgbBlueViolet
    rgbBrown
    rgbBurlyWood
    rgbCadetBlue
    rgbChartreuse
    rgbCoral
    rgbCornflowerBlue
    rgbCornsilk
    rgbCrimson
    rgbDarkBlue
    rgbDarkCyan
    rgbDarkGoldenrod
    rgbDarkGray
    rgbDarkGreen
    rgbDarkGrey
    rgbDarkKhaki
    rgbDarkMagenta
    rgbDarkOliveGreen
    rgbDarkOrange
    rgbDarkOrchid
    rgbDarkRed
    rgbDarkSalmon
    rgbDarkSeaGreen
    rgbDarkSlateBlue
    rgbDarkSlateGray
    rgbDarkSlateGrey
    rgbDarkTurquoise
    rgbDarkViolet
    rgbDeepPink
    rgbDeepSkyBlue
    rgbDimGray
    rgbDimGrey
    rgbDodgerBlue
    rgbFireBrick
    rgbFloralWhite
    rgbForestGreen
    rgbFuchsia
    rgbGainsboro
    rgbGhostWhite
    rgbGold
    rgbGoldenrod
    rgbGray
    rgbGreen
    rgbGreenYellow
    rgbGrey
    rgbHoneydew
    rgbHotPink
    rgbIndianRed
    rgbIndigo
    rgbIvory
    rgbKhaki
    rgbLavender
    rgbLavenderBlush
    rgbLawnGreen
    rgbLemonChiffon
    rgbLightBlue
    rgbLightCoral
    rgbLightCyan
    rgbLightGoldenrodYellow
    rgbLightGray
    rgbLightGreen
    rgbLightGrey
    rgbLightPink
    rgbLightSalmon
    rgbLightSeaGreen
    rgbLightSkyBlue
    rgbLightSlateGray
    rgbLightSteelBlue
    rgbLightYellow
    rgbLime
    rgbLimeGreen
    rgbLinen
    rgbMaroon
    rgbMediumAquamarine
    rgbMediumBlue
    rgbMediumOrchid
    rgbMediumPurple
    rgbMediumSeaGreen
    rgbMediumSlateBlue
    rgbMediumSpringGreen
    rgbMediumTurquoise
    rgbMediumVioletRed
    rgbMidnightBlue
    rgbMintCream
    rgbMistyRose
    rgbMoccasin
    rgbNavajoWhite
    rgbNavy
    rgbNavyBlue
    rgbOldLace
    rgbOlive
    rgbOliveDrab
    rgbOrange
    rgbOrangeRed
    rgbOrchid
    rgbPaleGoldenrod
    rgbPaleGreen
    rgbPaleTurquoise
    rgbPaleVioletRed
    rgbPapayaWhip
    rgbPeachPuff
    rgbPeru
    rgbPink
    rgbPlum
    rgbPowderBlue
    rgbPurple
    rgbRed
    rgbRosyBrown
    rgbRoyalBlue
    rgbSalmon
    rgbSandyBrown
    rgbSeaGreen
    rgbSeashell
    rgbSienna
    rgbSilver
    rgbSkyBlue
    rgbSlateBlue
    rgbSlateGray
    rgbSnow
    rgbSpringGreen
    rgbSteelBlue
    rgbTan
    rgbTeal
    rgbThistle
    rgbTomato
    rgbTurquoise
    rgbViolet
    rgbWheat
    rgbWhite
    rgbWhiteSmoke
    rgbYellow
    rgbYellowGreen

    (The entries above are members of the Excel.XlRgbColor Class, which you can find in your vba Object browser)

    zeddy
    Last edited by zeddy; 2015-05-14 at 10:35.

  6. The Following 2 Users Say Thank You to zeddy For This Useful Post:

    Maudibe (2015-05-14),RetiredGeek (2015-05-14)

  7. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    ..and if you want the values of these rgb colour names, use the vba Immediate Window and type, for example
    ?rgbTurquoise
    ..to get the value.

    Or see attached file

    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2015-05-14 at 10:44.

  8. #6
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    I adapted the solution zeddy offered. It was not clear in the Original question that the data were arranged in a table. Therefore, I had to Ungroup the Sparklines to make it work:
    Range("tblDiv[TREND]").Sparklines.Ungroup
    An additional comment: as the procedure uses the Worksheet_Change event, the Sparklines must first be created before the Alpha values are introduced or changed. A little bit inconvenient, but I can live with that.
    Thanks zeddy.

  9. The Following User Says Thank You to rvWoody For This Useful Post:

    zeddy (2015-05-16)

  10. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi rvWoody

    Glad you sorted it.
    I liked your post#1 image very much, but I much prefer a sample file as well.
    Then we would see the Table, and would have seen whether the sparklines were all in one group or not. Thanks for following up and telling us what you did. That helps everyone. A well deserved thanks to you in my book.

    zeddy

  11. #8
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    The sample code above changes all sparklines in the Trend column, whether Alpha has been changed or not. It also contains cell references and depends on the Trend cell being located in front and adjacent to the Alpha column. In the 'final' code, I addressed these two problems. The data table is called tblDiv. The Alpha cells normally contain a formula (simulated here) that will be dragged along the entire column.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rngIntersect As Range
    Set rngIntersect = Intersect(Target, Range("tblDiv[Alfa]"))
    
    Select Case Not rngIntersect Is Nothing
        Case True
            SparklineColour rngIntersect
        Case False
    End Select
    
    End Sub
    Code:
    Private Sub SparklineColour(rngChanged As Range)
    'rngChanged contains only those Cells of he Alfa Range
    '   that are really changed/selected.
    '
    'INSPIRED by zeddy, Windows Secret Lounge - Spreadsheets
    '   Sparklines: Conditional Formatting
    
    Dim rngAlfa As Range
    Set rngAlfa = Range("tblDiv[Alfa]")
    
    Dim rngTrend As Range
    Set rngTrend = Range("tblDiv[Trend]")
    'Ungroup Sparklines in table
    rngTrend.SparklineGroups.Ungroup
    
    For Each cell In rngChanged
        'Where is the cell in the Alfa column?
        Dim lngAlfaOffset As Long
        lngAlfaOffset = cell.Row - rngAlfa.Row
        
        'The trendline will be in cell's row in the Trend Column
        'Offset's are zero based, but Cell references are 1 based!!
        With rngTrend.Cells(lngAlfaOffset + 1, 1).SparklineGroups.Item(1).SeriesColor
            
            Select Case cell.Value
                Case Is > 0
                .Color = rgbLightGreen
                Case 0
                .Color = rgbLightSkyBlue
                Case Is < 0
                .Color = rgbLightCoral
            End Select
            
        End With
        
    Next
    
    End Sub
    Attached Files Attached Files

  12. #9
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    The 'final' code posted above suffered in a reality check from two major problems:
    1. The change event is not fired for a calculated value. In the real spreadsheet, the Alpha cells contain a (user) function. If the function is recalculated, the change event does not fire.
    This is easily corrected: instead of choosing the Alpha column as the trigger, we choose the columns that provide the input parameters to the function. After all, the function's value only changes if the input to the function is changed.

    The worksheet_change event is changed to:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    'the change event precedes the calculate event
    Dim rngValue As Range
    Set rngValue = Union(Range("tblDIV[2013]"), _
                        Range("tblDIV[2014]"), _
                        Range("tblDIV[2015]"))
    
    'Check if a Dividend has changed
    Dim rngIntersect As Range
    Set rngIntersect = Intersect(Target, rngValue)
    
    Select Case Not rngIntersect Is Nothing
        Case True
            SparklineColour rngIntersect
        Case False
    End Select
    
    blnCalculateSort = True
    
    End Sub
    2. The spreadsheet's user had the unfortunate idea to sort the Name column in a different order or to Filter it. Guess what? The Sparkline's formatting sticked to the Original cell and did not follow the value. This was really a nasty one as there is no Sort/Filter event to intercept. Googling brought me to: Handling Sort and Filter Events in Excel. The article suggest to use the Calculate event. However the Calculate event has no input parameter, so you have to reformat the entire Sparkline column. In principle, you can use the Calculate event in stead of the Change event to perform the task at hand. The drawback is that you have to reformat the entire Sparkline column each time. As zeddy did in his first reply. Back to square one.

    The 'final' solution I implemented goes as follows: A sort/filter operation does NOT invoke a change event. This means that if the change event is fired, then there was no Sort/Filter operation and I can use the previous solution. If the Calculate event is fired without a preceding Change, then there was a possible sort/filter. In that case I invoke the SparklineColour sub with as input parameter the entire Alpha column. A Public variable blnCalculateSort is defined is a separate module and used to track the change event.

    The worksheet's code is as follows:
    Code:
    Private Sub Worksheet_Calculate()
    
    Select Case blnCalculateSort
        Case True
            'there was a change event - no re-colouring needed
        Case False
            'no change event - possible sort or filter operation
            'use the Alfa column as the 'changed' range
            SparklineColour Range("tblDIV[Alfa]")
    End Select
    
    'reset global variable
    blnCalculateSort = False
    
    End Sub
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    'the change event precedes the calculate event
    Dim rngValue As Range
    Set rngValue = Union(Range("tblDIV[2013]"), _
                        Range("tblDIV[2014]"), _
                        Range("tblDIV[2015]"))
    
    'Check if a Dividend has changed
    Dim rngIntersect As Range
    Set rngIntersect = Intersect(Target, rngValue)
    
    Select Case Not rngIntersect Is Nothing
        Case True
            SparklineColour rngIntersect
        Case False
    End Select
    
    blnCalculateSort = True
    End Sub
    The solution will probably not win a beauty contest, but for the moment all is quiet on the user front.
    Attached Files Attached Files

  13. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    A sort/filter operation does NOT invoke a change event.
    When using a filter, this typically changes the number of visible rows, so you can use a formula cell to detect this, and then use this as a 'trigger event'.

    Similarly, it is also possible to use a 'formula cell' that can tell if a sort operation has occurred.

    zeddy

  14. #11
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by zeddy View Post
    ... so you can use a formula cell to detect this...
    But 'formula' cells do not trigger a change event? There is something I don't undderstand.

  15. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi rvWoody

    OK. It works something like this. The User does something. Like change a cell value. For example, they could change a dropdown value in a cell, enter a value in a cell, or change a filter selection. Whatever. So a change event has occurred.

    When this change event is detected, we check the value of a specified cell that tells us (via a formula) how many visible rows there are currently in the filtered data range. We compare this with a previously-saved value (held in another cell). If they are different, then we can say we have essentially 'detected a filter event'. etc etc etc.

    zeddy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •