VBA - apply border to active cell

[hrayani]

Hello Friends,

I am using the below VBA code..
I want whole thick red color border on the active cell.
Like if cell F2 is the active cell then border on cell F2 only and remove borders if any from non active cell within the range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("C2:F2")) Is Nothing Then
    Range("H2").Value = ActiveCell.Value
End If

If Not Intersect(Target, Range("C3:F4")) Is Nothing Then
    Range("H3").Value = ActiveCell.Value
End If

End Sub

Regards,

Humayun

 

[johnnyL]

This is the best that I could do for you now :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
    Static PreviousTarget As Range
'
    If Not PreviousTarget Is Nothing Then
        PreviousTarget.Borders.Color = xlNone                   ' 'Erase' previous border color
    End If
'
    If Not Intersect(Target, Range("C2:F2")) Is Nothing Then    ' <--- Set this to the range you want
        Target.Borders.Color = vbCyan                           ' Cyan makes the Red border when mixed with the normal border of an active cell
        Target.Borders.Weight = xlMedium                        ' Thickness of border
'
        Set PreviousTarget = Target                             ' Save the current cell address so that it can be erased the next time
'
'
'       Any additional code that you want can go here
'
'
    End If
End Sub

That will highlight the active cell border red when the cell is within the range that you specify, and it will remove the red border from that cell when another cell is clicked on.

 

[hrayani]

Hi Johnny,

Thanks for the reply... But it does not work as required
It applies borders to the entire range and does not remove when a cell is not active

I have come up with a solution (with cell color i.e. addition to my initial requirement) myself with a bit of experimenting here & there
& this does serves the purpose

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("C2:F2")) Is Nothing Then

    'This removes cell color and border from the entire range
    Range("C2:F2").Interior.Pattern = xlNone
    Range("C2:F2").Borders.LineStyle = xlNone
   
    Range("H2").Value = ActiveCell.Value
  
   'This applies cell color to the active cell
    ActiveCell.Interior.ThemeColor = xlThemeColorAccent4
    ActiveCell.Interior.TintAndShade = 0.7   
   
    'This applies border to the active cell
    ActiveCell.Borders.Color = vbBlack
    ActiveCell.Borders.Weight = xlThick   
   
End If
End Sub