Conditional formatting update VBA for dynamic last column

lisaruddy1

New Member
Joined
Nov 14, 2014
Messages
3
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?

VBA Code:
    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.

VBA Code:
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
Try this.

VBA Code:
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

New Member
Joined
Nov 14, 2014
Messages
3
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.

formatting.JPG


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

Thanks,

Lisa
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
There's a missing ).
VBA Code:
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,117
Messages
5,640,208
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top