Prev: WorksheetFunction Calculation Error
Next: Manadatory break after 10 days of work formulae
From: Nina on 9 Mar 2010 14:50
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
> .
>
From: Dave Peterson on 9 Mar 2010 15:36
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
 | 
Pages: 1
Prev: WorksheetFunction Calculation Error
Next: Manadatory break after 10 days of work formulae