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?
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.
Thank you for any assistance you could provide.
Thanks,
Lisa
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