Feedback by UserVoice

Hugh John Cook

My feedback

  1. 29 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Hugh John Cook commented  · 

    I completely understand that. However, one can wait a very long time for Microsoft to add or improve functionality for which there is an "obvious" need. So in the meantime, I'm offering a workaround to those who aren't avoiding VBA, or who might be tempted to try it if they can see some value to doing so. :-)

    By the way, since my post yesterday, I have learned that ActiveCell.Interior.ColorIndex will return xlColorIndexNone if a cell has "no fill", and this information can be used to reset the color to xlNone rather than colour_cur, if appropriate, which will solve the problem I identified with my previous solution.

    Cheers,
    HJ

    An error occurred while saving the comment
    Hugh John Cook commented  · 

    Below is some VBA code that will do what you want. It's a bit clunky because you have to deal with each edge of a cell as a separate border. Also, there doesn't seem to be a way in VBA to simply display a palette and return the selected color, so I have called the dialog to change the cell background color, then extract the color information from the cell. It's easier to use this than the border dialog because it deals with only a single attribute, but then you have to restore the original cell color, and there is apparently no way to distinguish between white and "no fill". Therefore, if the active cell had no fill, it will be changed to white, which will obscure the surrounding lines if that particular cell didn't have borders to begin with. So, you might have to change from white back to "no fill" in that case. (This only affects the active cell.)

    Finally, if you select the entire worksheet, this code takes a very long time to execute, so I recommend you don't do that....

    I hope this is somewhat helpful.
    HJ

    '*******************************************************************************
    '*
    Sub ChangeCellBorderColour()
    '* ---------------------------
    '*******************************************************************************
    '*
    '* this macro changes the colour of every cell border in a selected range that
    '* is one particular colour to a second colour that is selected from a palette
    '*

    Dim cel As Range

    Dim rng As String

    Dim colour_cur As Long
    Dim colour_new As Long

    ' turn off screen updating to avoid flicker and speed up the process
    Application.ScreenUpdating = False

    ' save original colour of current cell
    colour_cur = ActiveCell.Interior.Color
    rng = Selection.Address
    ActiveCell.Select

    ' select new colour for cells to be changed, but use interior cell colour
    Application.Dialogs(xlDialogPatterns).Show
    colour_new = ActiveCell.Interior.Color

    ' reset colour of current cell
    ActiveCell.Interior.Color = colour_cur

    ' loop through each cell in the selected range, test each border to see if
    ' it exists and, if so, then change the colour; border style will be preserved

    For Each cel In Range(rng)

    With cel.Borders(xlEdgeLeft)
    If .LineStyle <> xlNone Then
    .Color = colour_new
    End If
    End With

    With cel.Borders(xlEdgeTop)
    If .LineStyle <> xlNone Then
    .Color = colour_new
    End If
    End With

    With cel.Borders(xlEdgeBottom)
    If .LineStyle <> xlNone Then
    .Color = colour_new
    End If
    End With

    With cel.Borders(xlEdgeRight)
    If .LineStyle <> xlNone Then
    .Color = colour_new
    End If
    End With

    With cel.Borders(xlDiagonalDown)
    If .LineStyle <> xlNone Then
    .Color = colour_new
    End If
    End With

    With cel.Borders(xlDiagonalUp)
    If .LineStyle <> xlNone Then
    .Color = colour_new
    End If
    End With

    Next cel

    ' turn screen updating back on
    Application.ScreenUpdating = True

    End Sub

    '*******************************************************************************

Feedback and Knowledge Base