none
VBA-need to cycle through fill colors RRS feed

  • Question

  • I need to create a shortcut that changes a cell color to the next in a series of colors. I tried starting with a macro but after trying a few different syntax I am unable to get it to work. My thought is each time the macro runs it will change the selected cell(s) to the next color in the sequence - white>blue>purple>yellow>white - but if there is a different solution I'd be fine with that.

    Wednesday, March 30, 2016 3:52 PM

Answers

  • Hi DanfromCT,

    If get you right you have a certain sequence of colors and would like to change an active cell/s color in a given order.

    I made a sample for you:

    https://www.dropbox.com/s/7snqwod0w63be1e/PivotTableFilterChange.xlsm?dl=0

    There is a class ColorSeries that has a single method NextColor, which changes the color for an active cell/s in a sequence that is defined in Class_Initialize procedure.

    The ColorSeries class:

    Option Explicit
    Private col As Collection
    
    Public Sub NextColor()
        Dim rng As Range
        On Error Resume Next
        Dim v
        Set rng = Application.Selection
        If Err <> 0 Then
            Err.Clear
            Exit Sub
        Else
            v = col.Item(CStr(rng.Interior.Color))
            If Err <> 0 Then
                'There is not such a Color in a series
                Err.Clear
                Exit Sub
            End If
            'Color the active cells
            rng.Interior.Color = v
        End If
    End Sub
    
    
    Private Sub Class_Initialize()
    'Define the series
        Set col = New Collection
        '15773696 - blue is next when white is current
        col.Add 15773696, "16777215"  ' - white
        '10498160 - purple is next when blue is current
        col.Add 10498160, "15773696"  ' - blue
        '65535 - yellow is next when purple is current
        col.Add 65535, "10498160"  ' - purple
        '16777215 - white is next when yellow is current
        col.Add 16777215, "65535"  ' - yellow
    End Sub
    

    In a general module:

    Option Explicit
    Dim cs As New ColorSeries
    
    Public Sub Button1_Click()
        cs.NextColor
    End Sub
    

    If you need to add other colors in the sequence then modify Class_Initialize procedure to meet your needs.

    With regards,

    Sergiy Vakshul

    • Marked as answer by DanfromCT Thursday, March 31, 2016 2:42 PM
    Wednesday, March 30, 2016 8:54 PM
  • Hi, DanfromCT

    According to your description, you could refer to below code:
    Sub DemoCycleColor()
        Select Case ActiveCell.Interior.Color
            Case 16777215
                Selection.Interior.Color = 15773696
            Case 15773696
                Selection.Interior.Color = 10498160
            Case 10498160
                Selection.Interior.Color = 65535
            Case 65535
                Selection.Interior.Color = 16777215
        End Select
    End Sub

    • Marked as answer by DanfromCT Thursday, March 31, 2016 2:41 PM
    Thursday, March 31, 2016 3:08 AM

All replies

  • Hi DanfromCT,

    If get you right you have a certain sequence of colors and would like to change an active cell/s color in a given order.

    I made a sample for you:

    https://www.dropbox.com/s/7snqwod0w63be1e/PivotTableFilterChange.xlsm?dl=0

    There is a class ColorSeries that has a single method NextColor, which changes the color for an active cell/s in a sequence that is defined in Class_Initialize procedure.

    The ColorSeries class:

    Option Explicit
    Private col As Collection
    
    Public Sub NextColor()
        Dim rng As Range
        On Error Resume Next
        Dim v
        Set rng = Application.Selection
        If Err <> 0 Then
            Err.Clear
            Exit Sub
        Else
            v = col.Item(CStr(rng.Interior.Color))
            If Err <> 0 Then
                'There is not such a Color in a series
                Err.Clear
                Exit Sub
            End If
            'Color the active cells
            rng.Interior.Color = v
        End If
    End Sub
    
    
    Private Sub Class_Initialize()
    'Define the series
        Set col = New Collection
        '15773696 - blue is next when white is current
        col.Add 15773696, "16777215"  ' - white
        '10498160 - purple is next when blue is current
        col.Add 10498160, "15773696"  ' - blue
        '65535 - yellow is next when purple is current
        col.Add 65535, "10498160"  ' - purple
        '16777215 - white is next when yellow is current
        col.Add 16777215, "65535"  ' - yellow
    End Sub
    

    In a general module:

    Option Explicit
    Dim cs As New ColorSeries
    
    Public Sub Button1_Click()
        cs.NextColor
    End Sub
    

    If you need to add other colors in the sequence then modify Class_Initialize procedure to meet your needs.

    With regards,

    Sergiy Vakshul

    • Marked as answer by DanfromCT Thursday, March 31, 2016 2:42 PM
    Wednesday, March 30, 2016 8:54 PM
  • Hi, DanfromCT

    According to your description, you could refer to below code:
    Sub DemoCycleColor()
        Select Case ActiveCell.Interior.Color
            Case 16777215
                Selection.Interior.Color = 15773696
            Case 15773696
                Selection.Interior.Color = 10498160
            Case 10498160
                Selection.Interior.Color = 65535
            Case 65535
                Selection.Interior.Color = 16777215
        End Select
    End Sub

    • Marked as answer by DanfromCT Thursday, March 31, 2016 2:41 PM
    Thursday, March 31, 2016 3:08 AM