VBA to select one column to another column

Question

How do i write a macro to select one column to another one column with values?&nbsp;For example; i have values from cell A1 to N1, would like to select column from A to N instead of just column N&nbsp;This is my code so far which only highlights column N.Dim ws              As Worksheet
Dim lastRow         As Long
Dim i               As Long
Dim lastColumn      As Integer
Dim j               As Long

Set ws = ThisWorkbook.Sheets("TCA_consumer")

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

For i = 2 To lastRow
    
    If Cells(i, 14) = "Retain" Then
        
        For j = 1 To lastColumn
            
            Cells(i, lastColumn).Interior.ColorIndex = 4
        Next j
        
    Else
        
        For j = 1 To lastColumn
            Cells(i, lastColumn).Interior.ColorIndex = 5
        Next j
        
    End If
    
Next i&nbsp;Appreciate the help in advance.

harun24hr · Accepted Answer

hrh_dash&nbsp;To improve performance of macro we must try to avoid too many loops. Give a try on below codes.&nbsp;Sub HighlightCells()
Dim ws              As Worksheet
Dim lastRow         As Long
Dim i               As Long
Dim lastColumn      As Integer
Dim j               As Long

Set ws = ThisWorkbook.Sheets("TCA_consumer")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
        For i = 2 To lastRow
            If Cells(i, 14) = "Retain" Then
                Range("A" &amp; i, Cells(i, lastColumn)).Interior.ColorIndex = 4
                  Else
                Range("A" &amp; i, Cells(i, lastColumn)).Interior.ColorIndex = 5
            End If
        Next i
End Sub&nbsp;&nbsp;

Forum Discussion

VBA to select one column to another column

Share

Resources