Cell border created with macro seems to be fixed to cell=ignores filter

Question

Dear all,

A tried to substitute conditional formatting function by writing a code. It seemed to be working, except one issue. After sorting the columns the cell border does not move with the original cell values. The CellBorder remains on the original cell, however the cell fill color seems to be moving. Is there something that could fix it?

Thank you!

Veronika

used code:

Sub conditional_formatting_on_metrics()

Dim xCell As Range

Dim CommentValue As String

Dim CommentRange As Range

Set CommentRange = Range("G:G")

For Each xCell In CommentRange

CommentValue = xCell.Value

Select Case CommentValue

Case "Red"

xCell.Interior.Color = RGB(192, 0, 0)

Case "LightGreen"

xCell.Interior.Color = RGB(226, 239, 218)

Case "Green"

xCell.Interior.Color = RGB(112, 173, 71)

End Select

Next xCell

Set CommentRange = Range("H:I")

For Each xCell In CommentRange

CommentValue = xCell.Value

Select Case CommentValue

Case "Red"

xCell.Interior.Color = RGB(192, 0, 0)

Case "Red Warning"

xCell.Interior.Color = RGB(237, 125, 49)

xCell.Borders.LineStyle = Excel.XlLineStyle.xlContinuous

xCell.Borders.Color = RGB(192, 0, 0)

Case "Amber Warning"

xCell.Interior.Color = RGB(226, 239, 218)

xCell.Borders.LineStyle = Excel.XlLineStyle.xlContinuous

xCell.Borders.Color = RGB(237, 125, 49)

Case "Green"

xCell.Interior.Color = RGB(112, 173, 71)

End Select

Next xCell

End Sub