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!

 

[BCsehi]

I've only glanced at this thread but does this update to your posted code does what you want

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

change assumes that you have no other CF on the sheet

Dave

Perfect! Thank you so much. I have a special needs person entering data and this will make it so much easier for him to follow his entry cells! I am so impressed with you for sticking with this. So appreciate it!

 

[Peter_SSs]

Perfect! Thank you so much.

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.

 

[dmt32]

Perfect! Thank you so much. I have a special needs person entering data and this will make it so much easier for him to follow his entry cells! I am so impressed with you for sticking with this. So appreciate it!

Very kind of you but I just stepped in with an idea but as pointed out, suggestion does have some drawbacks which may or may not be an issue for your requirement

- glad we could help & appreciate the feedback.

Dave

 

[Micron]

This was my approach in case any future visitor to this thread finds it helpful. Row is shaded regardless if a cell or row selector is chosen. Prior formatting is removed. CF is not affected.

At the top of my sheet module:

Option Explicit
Dim lngLastActiveRow As Long

Worksheet selection event:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo errHandler
Application.EnableEvents = False
ActiveCell.EntireRow.Interior.Color = RGB(153, 204, 255)

If Not lngLastActiveRow = 0 Then
   With Rows(lngLastActiveRow).Interior
      .Pattern = xlNone
      .TintAndShade = 0
      .PatternTintAndShade = 0
   End With
End If

exitHere:
lngLastActiveRow = Target.Row
Application.EnableEvents = True
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

The error handler might seem like overkill but I prefer to use one if I disable application settings. You don't want errors leaving events turned off, for example.

 

[Peter_SSs]

Prior formatting is removed.

Just noting that the 'Undo' functionality is also removed.

 

[Micron]

Not sure if this addresses what you mean, but if you click in a cell in that row, it undoes. Not sure why - I didn't bother to figure it out. To be able to highlight that row again would require a slight code mod to trap where the row number equals the variable. Maybe I'll try that - maybe not. I suspect OP won't be using that code anyway.

 

[Peter_SSs]

Not sure if this addresses what you mean,

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.

 

[Micron]

OK, thanks - I see what you mean after testing. I see that there are complicated hacks out there for enabling undo after code makes changes, but it seems like a lot of bother if it's important to have.

 

[Peter_SSs]

I see that there are complicated hacks out there for enabling undo after code makes changes, but it seems like a lot of bother if it's important to have.

I suspect that you have not actually looked at the suggestion I made. If you do, you will see that there is nothing at all complex about it in this instance. The vba code is one single very short line and the conditional formatting is a simple formula easily applied to the worksheet.

 

[Micron]

I looked it over - twice. My comment about hacks is about vba for undoing, not your CF suggestion.