Consulting

Results 1 to 3 of 3

Thread: VBA Code for highlight a row based on cell with fix column and row

  1. #1
    VBAX Newbie
    Joined
    Jul 2018
    Posts
    1
    Location

    Question VBA Code for highlight a row based on cell with fix column and row

    I try to highlight a row on acive cell, but need some help
    I need col F to AS and row 3 to 500 highlight only!

    I tried this VBA code:

    Const maxoszlop As Long = 45
    Const vilagit As Long = 8
    Static rOld As Range
    Static nColorIndices(1 To maxoszlop) As Long
    Dim i As Long
    If Not rOld Is Nothing Then
    With rOld.Cells
    If .Row = ActiveCell.Row Then Exit Sub
    For i = 1 To maxoszlop
    .Item(i).Interior.ColorIndex = nColorIndices(i)
    Next i
    End With
    End If
    Set rOld = Cells(ActiveCell.Row, 1).Resize(1, maxoszlop)
    With rOld
    For i = 1 To maxoszlop
    nColorIndices(i) = .Item(i).Interior.ColorIndex
    Next i
    .Interior.ColorIndex = vilagit
    End With

    Thanks!

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,307
    Not quite sure what you want to do when the active cell is outside of columns F:AS, nor whether you want to lose highlighting if you go outside that area but maybe:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Const maxoszlop As Long = 40
    Const vilagit As Long = 8
    Static rOld As Range
    Static nColorIndices(1 To maxoszlop) As Long
    Dim i As Long
    
    If Not rOld Is Nothing Then
      With rOld.Cells
        If .Row = ActiveCell.Row Then Exit Sub
        For i = 1 To maxoszlop
          .Item(i).Interior.ColorIndex = nColorIndices(i)
        Next i
      End With
      Set rOld = Nothing
    End If
    If ActiveCell.Row > 2 And ActiveCell.Row < 501 Then
      Set rOld = Cells(ActiveCell.Row, "F").Resize(1, maxoszlop)
      With rOld
        For i = 1 To maxoszlop
          nColorIndices(i) = .Item(i).Interior.ColorIndex
        Next i
        .Interior.ColorIndex = vilagit
      End With
    End If
    End Sub
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Expert
    Joined
    Sep 2016
    Posts
    636
    Location
    https://trumpexcel.com/highlight-act...-column-excel/


    1)Conditional Formatting (F3:AS500)
    =CELL("ROW")=ROW()

    or

    =AND(CELL("ROW")=ROW(),CELL("COL")>5,CELL("COL")<41)

    2)sheet module
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Application.ScreenUpdating = True
    End Sub
    Last edited by mana; 07-11-2018 at 05:46 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •