Excel Macro Creation - How do I conditionally format all rows regardless of row count?

Question

Hi,

I extract data every day from a database which contains multiple rows of data and multiple columns. I have created a macro to format all the data with conditional formatting per column. When I created the macro, I 'asked' or coded the macro to conditionally format the column to the end or bottom of the column (the last row).

The issue I have is there are always new rows of data added to the data extraction. When my macro runs, it will conditionally format to the last row number which existed when I originally created the macro. For example, when I created the macro, the last row number was 10. When I pull the data a week later, the last row number is 15. When the macro runs, it only formats to row 10. Rows 11-15 are left unformatted.

My solution is to format the entire column. However, the conditional formats I use format the blank rows as well as the data rows. This is visually unacceptable.

How do I code the macro to always go to the bottom row of data regardless of where that row was when I originally created the macro?

Answer 1

Let's say the column you want to format is column C. You can use code like this - you'll have to modify it to suit your purpose.

    Dim LastRow As Long
    LastRow = Range("C" & Rows.Count).End(xlUp).Row
    With Range("C2:C" & LastRow).FormatConditions
        .Delete
        With .Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="=37")
            .Interior.Color = vbRed
        End With
    End With