auto highlight row

rjplante

Active Member
Joined
Oct 31, 2008
Messages
477
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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
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

Active Member
Joined
Oct 31, 2008
Messages
477
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
 

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,003

ADVERTISEMENT

Hi Robert

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

Code:
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
Code:
'  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

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
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

Code:
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

Well-known Member
Joined
Mar 15, 2013
Messages
989
Office Version
  1. 2010
Platform
  1. Windows
Adapting from the discussion between Peter_SSs and Rick Rothstein,
maybe something like this ?
Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,492
Messages
5,523,260
Members
409,506
Latest member
reneekeane

This Week's Hot Topics

Top