PDA

View Full Version : VBA Code for highlight a row based on cell with fix column and row



PCha0s
07-10-2018, 03:42 PM
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! :think:

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!

p45cal
07-11-2018, 05:01 AM
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

mana
07-11-2018, 05:25 AM
https://trumpexcel.com/highlight-active-row-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