Conditional formatting update VBA for dynamic last column

[lisaruddy1]

Hello,

I have taken over managing some data sets for my company and I am using documents which have been in use for some time.

The macro in one report will do the necessary changes to the document and then will apply conditional formatting. The problem I have is that the data used to be within a set range and now is dynamic and can change daily.

In the code below, is it possible to change the L1000 to reflect the last column with a predefined row?

    Range("A7:L1000").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A7=1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

I have another piece of VBA that I use to help drag a set formula across to the last column but I am not sure how I would merge these two so the columns.count information could be used here also.

Dim lastcolumn As Long

lastcolumn = Cells(6, Columns.Count).End(xlToLeft).Column

Range("C2").AutoFill Destination:=Range(Cells(2, 3), Cells(2, lastcolumn))

Thank you for any assistance you could provide.

Thanks,

Lisa

 

[Norie]

Try this.

Dim lastcolumn As Long

lastcolumn = Cells(6, Columns.Count).End(xlToLeft).Column

Range("C2").AutoFill Destination:=Range(Cells(2, 3), Cells(2, lastcolumn)

With Range("A7", Cells(1000, lastcolumn)
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$A7=1"
    .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False
End With

 

[lisaruddy1]

Hi Norie,

Thank you for the prompt response.

I have tried this but receive a syntax error. I tried removed the autofill section because I could deal with that separately, just to see if I could get the formatting to work. The error pops up on the With Range line. I have uploaded an image to show what it highlights.

Thank you for your assistance, I appreciate you taking the time to assist.

Thanks,

Lisa

 

[Norie]

There's a missing ).

Dim lastcolumn As Long

    lastcolumn = Cells(6, Columns.Count).End(xlToLeft).Column

    Range("C2").AutoFill Destination:=Range(Cells(2, 3), Cells(2, lastcolumn))

    With Range("A7", Cells(1000, lastcolumn))
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$A7=1"
        .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 49407
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With

 

[lisaruddy1]

Thank you Norie for your support, this works perfect!