Conditional formatting with macro

coop123

New Member
Joined
Dec 18, 2018
Messages
5
Hi

I have a macro which refomats an excel file. I want to highlight rows where value in far right column is greater than zero. Last column position is variable which I can select and apply conditional format to column, but how do I apply to complete row.

Suggestions would be much appreciated.

Coop123
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,600
Is the last column position always the same in every row?
 

coop123

New Member
Joined
Dec 18, 2018
Messages
5
Hi Yes it is same for all rows in file.

Each time the file is created the last column will be in a different position.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,600
Try:
Code:
Sub HighlightRows()
    Dim LastRow As Long, lCol As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lCol = ActiveSheet.UsedRange.Columns.Count
    With Cells(1).CurrentRegion
        .AutoFilter lCol, ">0"
        Range(Cells(2, 1), Cells(LastRow, lCol)).SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 6
    End With
    Cells(1).AutoFilter
End Sub
 

Forum statistics

Threads
1,085,056
Messages
5,381,461
Members
401,740
Latest member
jphermans

Some videos you may like

This Week's Hot Topics

Top