Advice for edit to existing working code

[ipbr21054]

Afternoon,
I have a worksheet where i have certain rows a certain color.

I need to have the active cell WHITE once selected BUT when i leave the cell the cell interior color reverts back to its color BEFORE it was selected.
The below code im using does exactly that BUT in a few cells there will be the text TBA & the interior color will be RED.
I was using conditional formatting to control a cell with TBA in it to be changed RED but the code below kills it.
I then need to manually select the cell & select RED from the toolbar fill color option.
Clicking the RED cell changes it to WHITE & when i leave the cell reverts back to RED but need to then remember to apply the RED in the first place,if i explained correctly

Can you see a possible edit or workaround to get this working.

Many thanks.

Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' THIS WILL COLOUR ACTIVE CELL & KEEP INTERIOR COLOUR ONCE LEFT HAS BEEN LEFT
    Dim myStartCol As String
    Dim myEndCol As String
    Dim myStartRow As Long
    Dim myLastRow As Long
    Dim myRange As Range

    If Target.Cells.Count > 1 Then Exit Sub
    
    Application.ScreenUpdating = False
    
'   *** Specify columns to apply this to ***
    myStartCol = "A"
    myEndCol = "K"

'   *** Specify start row ***
    myStartRow = 8
    
'   Use first column to find the last row
    myLastRow = Cells(Rows.Count, myStartCol).End(xlUp).Row
    
'   Build range to apply this to
    Set myRange = Range(Cells(myStartRow, myStartCol), Cells(myLastRow, myEndCol))
    
    '   Check to see if cell selected is outside of range
    If Intersect(Target, myRange) Is Nothing Then Exit Sub
    
    With Target
        .Worksheet.Cells.FormatConditions.Delete
        .FormatConditions.Add xlExpression, , True
        .FormatConditions(1).Interior.Color = vbWhite
    End With
End Sub

 

[Snakehips]

@ipbr21054 Can you tell me......
Do all cells in your range of interest, A8:A??, have the same Interior colour?
If so, what is that colour?
Is the "TBA" = Red the only conditional formatting applied to that range?

 

[ipbr21054]

Example,
Row 8 is say Green
Row 9 is say Green
Row 10 is say Blue
Row 11 is say Yellow

TBA is conditional formatting to column H

 

[Snakehips]

This is not easy. Is TBA a text entry or a formula result?

 

[Snakehips]

Hopefully, it is text rather than formula? Inwhich case, this is my best shot.

In a Code Module, declare Prev as a global variable.

Global Prev As Range

Then try the following event code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' THIS WILL COLOUR ACTIVE CELL & KEEP INTERIOR COLOUR ONCE LEFT HAS BEEN LEFT
    Dim myStartCol As String
    Dim myEndCol As String
    Dim myStartRow As Long
    Dim myLastRow As Long
    Dim myRange As Range
    Dim c As Integer
    
 '** Note Prev is a Global variable as range  Declared in a code Module ****
 
Application.EnableEvents = False 'prevent changes made by this code tiggering an endless loop
On Error Resume Next  'Ignore error if Prev is nothing
'reestablish previous cell colour
    Prev.Formula = Trim(Prev.Formula)  'remove any end space applied by previous code
    
    'set column offset c to enable reinstating correct row colour of Prev
    Select Case Prev.Column
        Case 1 To 10
        c = 11 - Prev.Column
        Case 11
        c = -1
     End Select
    ' reinstate row colour and set Prev to nothing
    Prev.Interior.Color = Prev.Offset(, c).Interior.Color
    Set Prev = Nothing

On Error GoTo 0  'reset default error handling

    If Target.Cells.Count > 1 Then GoTo Quit
    
    Application.ScreenUpdating = False
    
'   *** Specify columns to apply this to ***
    myStartCol = "A"
    myEndCol = "K"

'   *** Specify start row ***
    myStartRow = 8
    
'   Use first column to find the last row
    myLastRow = Cells(Rows.Count, myStartCol).End(xlUp).row
    
'   Build range to apply this to
    Set myRange = Range(Cells(myStartRow, myStartCol), Cells(myLastRow, myEndCol))
    
    '   Check to see if cell selected is outside of range
    If Intersect(Target, myRange) Is Nothing Then GoTo Quit
    
   'add end space to fool conditional formatting
    If UCase(Target) = "TBA" Then Target = Target & " "
    ' make selected cell white
    Target.Interior.Color = vbWhite
    'Set Prev to Active cell
    Set Prev = Target
    
Quit:
 Application.ScreenUpdating = True
 Application.EnableEvents = True
 
End Sub

Hope that helps.