modify current VBA from cell to row

[BCsehi]

Here is the code I am using to highlight the active cell in Excel and then keeping the original formatting:

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

I want to change this from just highlighting the active cell to the active row. Can someone help me with this? As you've probably guessed, I am not w VBA user! Thanks!

 

[Peter_SSs]

My comment about hacks is about vba for undoing, not your CF suggestion.

Fair enough. I had presumed that your comment was related to this thread.

 

[BCsehi]

A few things to consider.

• As @dmt32 already noted, the code assumes that no other conditional formatting exists in the worksheet. If it does, this code will wipe it out. The method that I linked to in post 6 does not wipe out existing conditional formatting.
• The method that I suggested does only highlight the row of the active cell whereas the post 10 code highlights all rows of the selection. I'm not sure which option you would want in that circumstance.
• You mentioned that a special needs person would be using this worksheet. I'm guessing then that they may be a little more prone to making mistakes? The code from post 10 disables the Undo option in Excel. The method that I linked to does not, so that may also be a consideration for you.

• No, that isn't what I mean. I mean that if the user selects, say C5, which contains some long text and they replace that text with "abc" but then decide that was a mistake and want to reinstate the long text, they cannot click 'Undo' on the ribbon. With the suggestion from my post, they can.

  Peter, this is the code I am using:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
Target.EntireRow.FormatConditions.Add(Type:=xlExpression, Formula1:=True).Interior.Color = vbYellow
End Sub

It works great, but If I can get the undo button to still work, everything will be be perfect. Hope this is an easy fix. You indicate that the method you link to doesn't disable the undo button, but using the above code does.

 

[Peter_SSs]

.. If I can get the undo button to still work, everything will be be perfect. Hope this is an easy fix. You indicate that the method you link to doesn't disable the undo button, but using the above code does.

So, have you tried tried the method that I linked to?

 

[BCsehi]

So, have you tried tried the method that I linked to?

I believe I have tried, but the undo icon is still grayed out....

 

[BCsehi]

So, have you tried tried the method that I linked to?

While I think I've tried, I'm not sure what you mean by the "method you link to"?

 

[Peter_SSs]

I believe I have tried, but the undo icon is still grayed out....

Try this

1. Make a copy of your worksheet.
2. Remove any conditional formatting from the worksheet (Home ribbon tab - Conditional Formatting - Clear Rules - Clear Rules from Entire Sheet)
3. Right-click the sheet's name tab and choose 'View Code'.
4. Remove any Worksheet_SelectionChange code (eg code like you have in post #22
   
   

   I'm not sure what you mean by the "method you link to"?

5. In post #6 I provided a link to another post in the forum, but here it is again highlight row and column
   Go to that other post
6. Carefully follow the numbered steps 1 to 6 but leave out step 3