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!

 

[Micron]

I'm more adept at Access vba. Sometimes when I want to figure out something in Excel vba I use the macro recorder and adapt the results. Or I Google something (like in this case, "Excel vba highlight row". Just saying, because I actually don't have code for this but presume it would be to refer to Target.Row. However, I don't see how what you have works because you don't SET the range variable to anything. I do see why you're not aware of any issue that would arise from that, and that's because of Resume Next statement.

 

[Micron]

10 minutes of searching and a simple test and this seems to do what you're asking for:
Target.EntireRow.Interior.ColorIndex = 37

It's probably not what you really want though. Highlighting the row will go right to the very end column.

 

[BCsehi]

10 minutes of searching and a simple test and this seems to do what you're asking for:
Target.EntireRow.Interior.ColorIndex = 37

It's probably not what you really want though. Highlighting the row will go right to the very end column.

could you please tell me where I put this (your suggestion) within the code that I am currently using? It sounds like exactly what I want....

 

[Micron]

maybe right after this line: Static oPrev As Range

which as I said, makes no sense to me, nor does the Resume Next. If you have no clue about vba as you've indicated then perhaps you ought to research those 2 topics:
Declaring object variables
and
On Error statement
to see if they fit your requirements as you've used them.

 

[Peter_SSs]

I want to change this from just highlighting the active cell to the active row. Can someone help me with this?

You could try the 6 numbered steps in this post, though leave out step number 3.

 

[BCsehi]

maybe right after this line: Static oPrev As Range

which as I said, makes no sense to me, nor does the Resume Next. If you have no clue about vba as you've indicated then perhaps you ought to research those 2 topics:
Declaring object variables
and
On Error statement
to see if they fit your requirements as you've used them.

Not what I'm looking for. The code that I have in place now, highlights the "active cell" and then when I go to another "active cell", the former "active cell" reverts back to its original format and the new "active cell" is highlighted. All I want to change is from "active cell" to "active row". Thanks for trying to help, it is much appreciated.

 

[Micron]

So you want the code to highlight the active row but remove interior colour of whatever row had been previously coloured by this code? I only glanced through the suggested link so not sure if it will help you or not. Will wait to find out.

 

[BCsehi]

So you want the code to highlight the active row but remove interior colour of whatever row had been previously coloured by this code? I only glanced through the suggested link so not sure if it will help you or not. Will wait to find out.

Yes, but when a new cell is active, the former reverts to all of its previous formatting.

 

[dmt32]

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