Personal.xlsb = ROW()=CELL("row")

[Jon760]

Hello,

I currently have conditional formatting using =ROW()=CELL("row") to highlight/underline the entire row of an active cell. As I don't the most powerful PC and my workbook now have several thousand rows it's lagging so I was wondering whether I could store this formula within a macro or create a new macro that can be stored in Personal.xlsb so that the macro is detached from the active workbook. The underline command does not have to be automatic when moving up or down row. It can be manually activated via shortcut or F9 calc.

Thank you for any help.

 

[Jeffrey Mahoney]

I have done this a lot. This method doesn't lag. I know it seems like too much to do, but it works. What makes it work more efficiently is using the ActiveSheet.EnableFormatConditionsCalculation command. No need to recalc.

Code placed in Sheet Module in the sheet you want to affect

Private Sub Worksheet_SelectionChange(ByVal target As Range)
  
    If CurRow <> target.Row And RowMvmt = False Then
      CurRow = target.Row
      RowMvmt = True
      OT = Now() + TimeSerial(0, 0, 0.25)
      Application.OnTime EarliestTime:=OT, procedure:="NewLine", schedule:=True
      Application.OnTime EarliestTime:=Now() + TimeSerial(0, 0, 1), procedure:="NewLineRefresh", schedule:=True
    ElseIf RowMvmt = True Then
      CurRow = target.Row
      RowMvmt = False
      On Error Resume Next
      Application.OnTime EarliestTime:=OT, procedure:="NewLine", schedule:=False
      On Error GoTo 0
    End If

End Sub

Code placed in Standard module

Public CurRow As Long
Public RowMvmt As Boolean
Public OT As Date

Sub NewLine()

  ActiveSheet.EnableFormatConditionsCalculation = False
  ActiveSheet.EnableFormatConditionsCalculation = True
  RowMvmt = False
  
End Sub

Sub NewLineRefresh()

  ActiveSheet.EnableFormatConditionsCalculation = False
  ActiveSheet.EnableFormatConditionsCalculation = True
  
End Sub

 

[Jeffrey Mahoney]

I don't think that using the Personal WB will work. No events can happen across workbooks