Advice for edit to existing working code

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,071
Office Version
  1. 2007
Platform
  1. Windows
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.

Rich (BB 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

    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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,355
Office Version
  1. 2013
Platform
  1. Windows
@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

Well-known Member
Joined
Nov 16, 2010
Messages
4,071
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
May 17, 2009
Messages
5,355
Office Version
  1. 2013
Platform
  1. Windows
This is not easy. Is TBA a text entry or a formula result?
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,355
Office Version
  1. 2013
Platform
  1. Windows
Hopefully, it is text rather than formula? Inwhich case, this is my best shot.

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

VBA Code:
Global Prev As Range

Then try the following event code.

VBA 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.
 

Forum statistics

Threads
1,141,000
Messages
5,703,652
Members
421,308
Latest member
NewBlood

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top