Share via

White Borders when a cell has a specific interior color

Anonymous
2018-04-26T19:15:20+00:00

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

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2018-04-27T03:33:26+00:00

    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

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

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments