Modify code from paste values to change font color / multiple rang
Hi,
I was wondering if you could help me out modify a code that currently paste values to change the font color to automatic or black and to add multiple ranges (new ranges are E8:E17, E24:E33,E40:E49 and E56:E81) The current code is below and works Great! Thanks to everyone that helped/will help me out!! Sub PVPrImpct() With Range("b48:b74").Offset(, Worksheets( _ "Summary by month MTD").Range("A4") - 1) .Value = .Value End With End Sub Thanks again! |
Modify code from paste values to change font color / multiple rang
I'm not sure if those ranges are before the offset or after...
Maybe something like this will give you an idea: Option Explicit Sub PVPrImpct() Dim myArea As Range Dim HowManyCols As Long Dim myRng As Range HowManyCols = Worksheets("Summary by month MTD").Range("A4").Value - 1 Set myRng = ActiveSheet.Range("b48:b74,E8:E17,E24:E33,E40:E49, E56:E81") For Each myArea In myRng.Offset(0, HowManyCols).Areas With myArea .Value = .Value End With Next myArea End Sub Nina wrote: Hi, I was wondering if you could help me out modify a code that currently paste values to change the font color to automatic or black and to add multiple ranges (new ranges are E8:E17, E24:E33,E40:E49 and E56:E81) The current code is below and works Great! Thanks to everyone that helped/will help me out!! Sub PVPrImpct() With Range("b48:b74").Offset(, Worksheets( _ "Summary by month MTD").Range("A4") - 1) .Value = .Value End With End Sub Thanks again! -- Dave Peterson |
Modify code from paste values to change font color / multiple
Hi Dave - thanks for your help. I have not tested yet, but instead of pasting
values I would need it to change the font color to black or automatic. Thank you so much again! "Dave Peterson" wrote: I'm not sure if those ranges are before the offset or after... Maybe something like this will give you an idea: Option Explicit Sub PVPrImpct() Dim myArea As Range Dim HowManyCols As Long Dim myRng As Range HowManyCols = Worksheets("Summary by month MTD").Range("A4").Value - 1 Set myRng = ActiveSheet.Range("b48:b74,E8:E17,E24:E33,E40:E49, E56:E81") For Each myArea In myRng.Offset(0, HowManyCols).Areas With myArea .Value = .Value End With Next myArea End Sub Nina wrote: Hi, I was wondering if you could help me out modify a code that currently paste values to change the font color to automatic or black and to add multiple ranges (new ranges are E8:E17, E24:E33,E40:E49 and E56:E81) The current code is below and works Great! Thanks to everyone that helped/will help me out!! Sub PVPrImpct() With Range("b48:b74").Offset(, Worksheets( _ "Summary by month MTD").Range("A4") - 1) .Value = .Value End With End Sub Thanks again! -- Dave Peterson . |
Modify code from paste values to change font color / multiple
Record a macro when you change the font color to automatic and I bet you'll see
where to add it to your code. If you need help, post back and explain what those ranges are (I'm still confused). Nina wrote: Hi Dave - thanks for your help. I have not tested yet, but instead of pasting values I would need it to change the font color to black or automatic. Thank you so much again! "Dave Peterson" wrote: I'm not sure if those ranges are before the offset or after... Maybe something like this will give you an idea: Option Explicit Sub PVPrImpct() Dim myArea As Range Dim HowManyCols As Long Dim myRng As Range HowManyCols = Worksheets("Summary by month MTD").Range("A4").Value - 1 Set myRng = ActiveSheet.Range("b48:b74,E8:E17,E24:E33,E40:E49, E56:E81") For Each myArea In myRng.Offset(0, HowManyCols).Areas With myArea .Value = .Value End With Next myArea End Sub Nina wrote: Hi, I was wondering if you could help me out modify a code that currently paste values to change the font color to automatic or black and to add multiple ranges (new ranges are E8:E17, E24:E33,E40:E49 and E56:E81) The current code is below and works Great! Thanks to everyone that helped/will help me out!! Sub PVPrImpct() With Range("b48:b74").Offset(, Worksheets( _ "Summary by month MTD").Range("A4") - 1) .Value = .Value End With End Sub Thanks again! -- Dave Peterson . -- Dave Peterson |
All times are GMT +1. The time now is 05:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com