VBA to highlight the activecell but restore previous color (if any) when cell loses focus

[WednesdayC]

Hi All

I have trawled the internet but am having trouble finding the answer to this one.

I would like the Worksheet_Selectionchange event to temporarily change the colour (say yellow) but then restore any existing colour (if appropriate) upon exiting the cell.

I am using Excel 2011 (Excel for Mac) if it makes any difference.

Please can anyone help me?

Many thanks in advance

Wednesday

 

[Jaafar Tribak]

Do you have cells with conditional formatting ? If not then you could use the following code

Code in the worksheet module :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static oPrev As Range
    
    On Error Resume Next
    oPrev.FormatConditions.Delete
    Target.FormatConditions.Add(Type:=xlExpression, Formula1:=True).Interior.Color = vbRed
    Set oPrev = Target
End Sub

 

[WednesdayC]

Hi Jaafar

It works a treat!

Thank you so much.

Have a great rest of weekend.

Regards
Wednesday

 

[Paul_C]

Do you have cells with conditional formatting ? If not then you could use the following code

Code in the worksheet module :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static oPrev As Range
   
    On Error Resume Next
    oPrev.FormatConditions.Delete
    Target.FormatConditions.Add(Type:=xlExpression, Formula1:=True).Interior.Color = vbRed
    Set oPrev = Target
End Sub

Dear Jaafar,

This code works beautifully. I am just a beginner to VBA. Would you mind explaining the logic of each step? Does "oPrev" retain the range address (or range address with formatting)? and "Set oPrev = Target"? Also would you please recommend a good text book to learn Excel VBA. Thank You.

 

[Jaafar Tribak]

Dear Jaafar,

This code works beautifully. I am just a beginner to VBA. Would you mind explaining the logic of each step? Does "oPrev" retain the range address (or range address with formatting)? and "Set oPrev = Target"? Also would you please recommend a good text book to learn Excel VBA. Thank You.

Hi Paul_C

Sorry, I missed your post.

The code simply adds conditional formatting to the activecell (ie:=Target)... The CF formula always evaluates to TRUE hence the CF color of the activecell always is set to Red.

Right before the SelectionChange event handler routine exits, the current active cell is stored in a Static variable (oPrev) for the subsequent range selection. This is needed so that the CF of the previous selected range is removed and the initial range color is displayed.

Bear in mind though that this approach for highlighting the current selection has a drawback : It assumes the user has no pre-existing CF set up on the worksheet.... If there is an existing CF, it will be inadvertently removed by the line : oPrev.FormatConditions.Delete.

I hope my explanation was helpful.

PS:

Does "oPrev" retain the range address (or range address with formatting)?

oPrev is a static range variable that retains the previously selected range object.

 

[littlepete]

hello

can i ask if the same is possible with cells that have conditional formatting ? thank you !!!

 

[littlepete]

i found the solution !!!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Static PrevCell As Range

If Target.Cells.Count > 1 Then Exit Sub

If Not PrevCell Is Nothing Then

PrevCell.Font.Color = RGB(0, 0, 0)

PrevCell.Font.Bold = False

End If

Selection.Cells.Font.Color = RGB(255, 255, 255)

Target.Font.Bold = True

Set PrevCell = Target

 

[BSALV]

- Every time you close your workbook, the last selected cell 'll remain red, so after a while ... !
- this is for the whole workbook ! Are you aware of that ? Perhaps you want it only for one or several sheets.

There was also a funny trick happening in VBA while he/she was examining the last row of the usedrange with specialcells. Apparently something within excel, but by doing so the selection change event is triggered (twice, i think ?). I'm not sure if that 'll bother your colors ?