White Borders when a cell has a specific interior color

Question

Hi Everyone,

I'm trying to add conditional formatting to a specified range of data that will put white borders around cells with a specific background color. I want this to act like conditional formatting, where it will automatically place the borders when a specific internal color is used (i.e. NOT an one-time executable macro). 

I need to be able to sort **and maintain this border around the colors** (A sort with a conditional format will move the cell color, but not the border). For this reason, I understand this must be accomplished with a macro/vba. If so, could anyone help with a macro/vba formula?

Thank!

Moved from: Office /Excel /Windows 10 /Office 2016

Answer 1

Try to expand on the sample code shared below:

Note: You need to put these lines (which fall between the '=========== lines)

          in a FOR NEXT loop so that it would cover all your cells.

'======================================

WORKBOOKABC.Sheets("PQRS").RANGE("A1").Select

IF WORKBOOKABC.Sheets("PQRS").RANGE("A1").Interior.Color = RGB(102,204,205) Then

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 16764006

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

    Selection.Borders(xlDiagonalDown).LineStyle = xlNone

    Selection.Borders(xlDiagonalUp).LineStyle = xlNone

    With Selection.Borders(xlEdgeLeft)

        .LineStyle = xlContinuous

        .ThemeColor = 1

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.Borders(xlEdgeTop)

        .LineStyle = xlContinuous

        .ThemeColor = 1

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.Borders(xlEdgeBottom)

        .LineStyle = xlContinuous

        .ThemeColor = 1

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.Borders(xlEdgeRight)

        .LineStyle = xlContinuous

        .ThemeColor = 1

        .TintAndShade = 0

        .Weight = xlThin

    End With

    Selection.Borders(xlInsideVertical).LineStyle = xlNone

    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

End If

'=================================