Help with highlight macro

[Demention]

Hi everyone,

Thanks for taking the time to look at my issue.

I have found some code to highlight the active selected cell. I've add a bit to change the font colour to be easier read. My issue is, how do I change it back to black when I select a different cell?

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
     
    Static strPrevAddress As String
     
    If Not strPrevAddress = vbNullString Then
        With Range(strPrevAddress)
            .Interior.ColorIndex = xlNone
            .Font.Bold = False
        End With
    End If
     
    With Target
        .Interior.ColorIndex = 23
        .Font.Bold = True
        .Font.ColorIndex = 2
    End With
     
    strPrevAddress = Target.Address
     
End Sub

Also, if at all possible, I have some cells that are already in bold and if I select that cell and move to another, the bold formatting is removed. How do I fix that?

Thanks again for taking a look.

 

[Demention]

On the other hand, I have something that works.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    
    Static strPrevAddress As String
    Static boolBoldFont As Boolean
    Static lngInterior As Long
    Static lngFontColour As Long
    Dim rngTempTarget As Range
    
    Application.EnableEvents = False
    If Not strPrevAddress = vbNullString Then
        With Range(strPrevAddress)
            .Interior.ColorIndex = lngInterior
            .Font.ColorIndex = lngFontColour
            .Font.Bold = boolBoldFont
        End With
    End If
    
    strPrevAddress = Target.Address
    
    Set rngTempTarget = Target.Cells(1)
    
    With rngTempTarget
        boolBoldFont = (.Font.Bold = True)
        lngInterior = .Interior.ColorIndex
        lngFontColour = .Font.ColorIndex
        .Interior.ColorIndex = 23
        .Font.ColorIndex = 2
        .Font.Bold = True
    End With
    Application.EnableEvents = True
    
End Sub

Thanks guys

 

[Demention]

Is there a way to implement an RGB value for the fill colour?

.Interior.ColorIndex = 23

Thanks

 

[Demention]

Removing "Index" and replacing the number with RGB value has worked.

Sorry about my rambling, I don't know a lot about VBA so still learning.

 

[Demention]

Final code that works

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
   
    Static strPrevAddress As String
    Static boolBoldFont As Boolean
    Static lngInterior As Long
    Static lngFontColour As Long
    Dim rngTempTarget As Range
   
    Application.EnableEvents = False
    If Not strPrevAddress = vbNullString Then
        With Range(strPrevAddress)
            .Interior.Color = lngInterior
            .Font.ColorIndex = lngFontColour
            .Font.Bold = boolBoldFont
        End With
    End If
   
    strPrevAddress = Target.Address
   
    Set rngTempTarget = Target.Cells(1)
   
    With rngTempTarget
        boolBoldFont = (.Font.Bold = True)
        lngInterior = .Interior.Color
        lngFontColour = .Font.ColorIndex
        .Interior.Color = RGB(47, 117, 182)
        .Font.ColorIndex = 2
        .Font.Bold = True
    End With
    Application.EnableEvents = True
   
End Sub

 

[Demention]

Was getting Runtime error 94 when clicking other columns.

Changed Long to Variant

Static lngFontColour As Long