Cell border created with macro seems to be fixed to cell =ignores filter, does not move

Dear all,

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

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

I'm afraid Excel has that. It moves the fill colors, but not the border.
The solution in the attachment is to add an volatile formula (DATE(), NOW(), RAND() or other) in the worksheet, which will cause a recalculation every time the worksheet changes, i.e. triggering the _Calculate event. And the event is designed to repaint the range.
In the code, I added a Stop command so that you can see how often the _Calculate event is triggered.
You can hide the volatile formula by hiding the entire column or formatting this one cell using a custom formatting code: ;;;
If the workbook contains many worksheets that contain formulas, the _Calculate event will trigger very often. Therefore, the condition in this event will probably need to be more elaborate, so that the repainting of the sheet only happens at the appropriate time, rather than continuously.

Artik

Thank you Artik for your confirmation of what I have been suspicious of ("Excel has it"). As I have to spread the file on other I decided to go without borders. Thank you for your time and effort, very appreciated.