Conditional formatting with macro

[coop123]

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]

Is the last column position always the same in every row?

 

[coop123]

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]

Try:

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

 

[coop123]

Hi mumps

That does exacly what I want.

Thanks

coop123

 

[mumps]

You are very welcome.