Hello,
I have a macro that copies and pastes conditional formatting into a range of cells on a sheet, then runs some more code on these cells. My issue is that the range is predefined (T11:FZ45) and is subject to change by the user. The predefined range was chosen as a likely maximum amount of cells that would ever have values. However, running my code on all these cells takes about 6-10 seconds and I want to cut this down as there is usually a lot less values then the predefined amount. Therefore, I want this range to be dynamic based on the the last cell that has a value (T11:Last Cell With Value). Is this possible? I'd also like to embed this in my current macro. See below.
Sub Button7_Click()
'~~> Defines last cell for ranges below
'~~> Runs conditional formatting on select cells
Sheets("Sheet1").[A11:A45].Copy: Sheets("Sheet1").[T11:FZ45].PasteSpecial xlPasteFormats
'~~> Places formatting into cell permanently and deletes conditional formatting
Dim mySel As Range, aCell As Range
Set mySel = Sheets("Sheet1").[T11:FZ45]
For Each aCell In mySel
With aCell
.Font.FontStyle = .DisplayFormat.Font.FontStyle
.Interior.Color = .DisplayFormat.Interior.Color
.Font.Strikethrough = .DisplayFormat.Font.Strikethrough
End With
Next aCell
mySel.FormatConditions.Delete
End Sub
Thank you,
I have a macro that copies and pastes conditional formatting into a range of cells on a sheet, then runs some more code on these cells. My issue is that the range is predefined (T11:FZ45) and is subject to change by the user. The predefined range was chosen as a likely maximum amount of cells that would ever have values. However, running my code on all these cells takes about 6-10 seconds and I want to cut this down as there is usually a lot less values then the predefined amount. Therefore, I want this range to be dynamic based on the the last cell that has a value (T11:Last Cell With Value). Is this possible? I'd also like to embed this in my current macro. See below.
Sub Button7_Click()
'~~> Defines last cell for ranges below
'~~> Runs conditional formatting on select cells
Sheets("Sheet1").[A11:A45].Copy: Sheets("Sheet1").[T11:FZ45].PasteSpecial xlPasteFormats
'~~> Places formatting into cell permanently and deletes conditional formatting
Dim mySel As Range, aCell As Range
Set mySel = Sheets("Sheet1").[T11:FZ45]
For Each aCell In mySel
With aCell
.Font.FontStyle = .DisplayFormat.Font.FontStyle
.Interior.Color = .DisplayFormat.Interior.Color
.Font.Strikethrough = .DisplayFormat.Font.Strikethrough
End With
Next aCell
mySel.FormatConditions.Delete
End Sub
Thank you,