auto highlight row

[rjplante]

I have been using a process outlined on the tech republic website (see link below) to highlight the selected row from column A to column L. In general the process works great, however I have found a problem. I don't know if the problem is with the process, or if it is with Excel 365.

https://www.techrepublic.com/articl...or-both-using-vba-and-conditional-formatting/

The above process uses VBA on the worksheet to update the term SelRow with the ActiveCell.Row.
The Name manager assigns SelRow to cell Z2 on my spreadsheet.
Then using conditional formatting and entering the formula [=Row(B3)=SelRow] will allow me to fill the cells from A8:L1008 when any cell in column L is selected.

My problem arises when I deleted a row near at the end of the list. I deleted the last row and shifted the cells up. Now if I select a row before the last row I deleted, only the row selected will highlight, no problem. If I select a row after the row I deleted, that row, and the one before it is highlighted. I tried deleting another row at the end of my list which was blank just to see what would happen. Now, anytime after that row is selected, the active row is selected, as well as the previous two. If I cannot figure this out, I will have to scrap the process as this will end up highlighting several rows throughout the worksheet and not just my active row, as data rows are deleted over time.

Any help to restore this process to its proper function would be great.

Thanks in advance!!

Robert

 

[Johnny C]

The problem is going to be a disconnect between your VBA and your conditional formatting.
When you go into Conditional formatting, does it have multiple versions of similar things?
When you have a range with a conditional format and you start deleting things Excel can start breaking it up so you end up with a load of Conditional Formats and the formulae in some might have gone wonky.
The way I would fix it is have a Worksheet_SelectionChange function in the sheet code, it runs every time you move the cursor. In that put code to delete all conditional formatting from the sheet and reapply it to the revised range. It will slow the spreadsheet down though, it depends on how much and if you can live with it. If it's a pain you could put the code into the ThisWorkbook object and run it inside any of the following Workbook events
Workbook_BeforeSave
Workbook_Open
Workbook_SheetActivate
Workbook_SheetCalculate
Workbook_SheetChange

HTH

 

[rjplante]

Since I am fairly new to VBA, can you provide the code needed to update the conditional formatting for the document. I like the idea of having that in my ThisWorkbook code window and called from the Workbook_SheetChange macro.

Thanks,

Robert

 

[NoSparks]

The method of highlighting the selected row as being discussed by 2 MrExcel MVPs in this post from 2016 may be of interest,
it doesn't use conditional formatting.
http://www.mrexcel.com/forum/excel-questions/953403-border-around-entire-row-selected-cell.html

 

[Johnny C]

Hi Robert

Something along these lines would work (I googled it and pasted so you could see the syntax)

With Selection
    'Delete any previous conditional formatting
    .FormatConditions.Delete
    
    'add conditional formatting to selected cell
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=" & Selection.Cells(1).Address(False, False) & "=SelRow"
    
    'Assigning Violet color for the conditional formatting
    .FormatConditions(1).Interior.ColorIndex = 39
    
End With

However Nosparks way might be better with a Worksheet_SelectionChange event in the sheet code

Something along the lines of

'  Work out the last used cell and clear all formatting 
LastColumn = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
LastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).row

With Range(cells(1,1),cells(LastRow,LastColumn)).Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
End With

'  select current row upto last used column
With Range(cells(target.row,1),cells(target.row,LastColumn)).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

 

[Johnny C]

Sorry try this. SpecialCells crashes Excel in Sheet code for some reason so you may need to find a way to get the last row/column

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  LastRow = Range("A1").CurrentRegion.Rows.Count
  LastColumn = Range("A1").CurrentRegion.Columns.Count

With Range(Cells(1, 1), Cells(LastRow, LastColumn)).Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
End With

'  select current row upto last used column
With Range(Cells(Target.Row, 1), Cells(Target.Row, LastColumn)).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

End Sub

 

[NoSparks]

Adapting from the discussion between Peter_SSs and Rick Rothstein,
maybe something like this ?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  On Error Resume Next
  With Application
    .FindFormat.Clear
    .ReplaceFormat.Clear
    .FindFormat.Interior.ColorIndex = 27
    .ReplaceFormat.Interior.ColorIndex = xlNone
    ActiveSheet.UsedRange.Replace What:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=True
    .FindFormat.Interior.ColorIndex = xlNone
    .ReplaceFormat.Interior.ColorIndex = 27
    Intersect(Target.EntireRow, ActiveSheet.UsedRange).Replace What:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=True
  End With
End Sub