I have a macro/code which will apply a different Conditional Format to
each of four specific consequitive cells. (R2:U2)

I also have this macro set up simple "Pick from the list" Data
Validation in those same cells.

Now I want to copy those four conditional formats and Data Validation
settings all the way down until the end of the data.

There is no data in any of these coloumns, but there is data one coloum
to the left.
The number of rows will vary considerably each time this macro is run.
If I were to do this by keyoard I would copy R2:U2,
go to Q2, [end][down][right], then
[shifton][right][right][right][end][up][down][enter]

I am sure that I can work this movement out in a macro with out too much
problem.

My biggest problem would be copying and pasting just conditional formats
and Data Validations settings.

Unless there is an all around easier way to do this?

This spreadsheet, (all 24 of them) will be coming from Access.

Phil

Dim LastRow as long

with worksheets("somesheetname")
lastrow = .cells(.rows.count,"Q").end(xlup).row
'apply your CF these:
.range("r2:r" & lastrow)...
.range("s2:s" & lastrow)...
.range("t2:t" & lastrow)...
.range("u2:u" & lastrow)...




Phil Smith wrote:
>
> I have a macro/code which will apply a different Conditional Format to
> each of four specific consequitive cells. (R2:U2)
>
> I also have this macro set up simple "Pick from the list" Data
> Validation in those same cells.
>
> Now I want to copy those four conditional formats and Data Validation
> settings all the way down until the end of the data.
>
> There is no data in any of these coloumns, but there is data one coloum
> to the left.
> The number of rows will vary considerably each time this macro is run.
> If I were to do this by keyoard I would copy R2:U2,
> go to Q2, [end][down][right], then
> [shifton][right][right][right][end][up][down][enter]
>
> I am sure that I can work this movement out in a macro with out too much
> problem.
>
> My biggest problem would be copying and pasting just conditional formats
> and Data Validations settings.
>
> Unless there is an all around easier way to do this?
>
> This spreadsheet, (all 24 of them) will be coming from Access.
>
> Phil

--

Dave Peterson

I think I get what to do here.

Question: When I copy CF around, it will automatically adjust the
formulas depending on my adressing.

I assume that I create my target formula for the first cell in the
range, and it will also automatically adjust as it goes down the range?

Also "with" requires an "end with," right?



Dave Peterson wrote:
> Dim LastRow as long
>
> with worksheets("somesheetname")
> lastrow = .cells(.rows.count,"Q").end(xlup).row
> 'apply your CF these:
> .range("r2:r" & lastrow)...
> .range("s2:s" & lastrow)...
> .range("t2:t" & lastrow)...
> .range("u2:u" & lastrow)...
>
>
>
>
> Phil Smith wrote:
>
>>I have a macro/code which will apply a different Conditional Format to
>>each of four specific consequitive cells. (R2:U2)
>>
>>I also have this macro set up simple "Pick from the list" Data
>>Validation in those same cells.
>>
>>Now I want to copy those four conditional formats and Data Validation
>>settings all the way down until the end of the data.
>>
>>There is no data in any of these coloumns, but there is data one coloum
>>to the left.
>> The number of rows will vary considerably each time this macro is run.
>>If I were to do this by keyoard I would copy R2:U2,
>>go to Q2, [end][down][right], then
>>[shifton][right][right][right][end][up][down][enter]
>>
>>I am sure that I can work this movement out in a macro with out too much
>>problem.
>>
>>My biggest problem would be copying and pasting just conditional formats
>>and Data Validations settings.
>>
>>Unless there is an all around easier way to do this?
>>
>>This spreadsheet, (all 24 of them) will be coming from Access.
>>
>>Phil
>
>

I would expect that to happen--just like excel adjusts the formulas in cells.

But conditional formatting is strange.

See John Walkenbach's site for an example:
http://spreadsheetpage.com/index.php...al_formatting/

And yep, you'll need to add an "end with" (along with lots more code <vbg>) to
make that suggestion close to useful.



Phil Smith wrote:
>
> I think I get what to do here.
>
> Question: When I copy CF around, it will automatically adjust the
> formulas depending on my adressing.
>
> I assume that I create my target formula for the first cell in the
> range, and it will also automatically adjust as it goes down the range?
>
> Also "with" requires an "end with," right?
>
> Dave Peterson wrote:
> > Dim LastRow as long
> >
> > with worksheets("somesheetname")
> > lastrow = .cells(.rows.count,"Q").end(xlup).row
> > 'apply your CF these:
> > .range("r2:r" & lastrow)...
> > .range("s2:s" & lastrow)...
> > .range("t2:t" & lastrow)...
> > .range("u2:u" & lastrow)...
> >
> >
> >
> >
> > Phil Smith wrote:
> >
> >>I have a macro/code which will apply a different Conditional Format to
> >>each of four specific consequitive cells. (R2:U2)
> >>
> >>I also have this macro set up simple "Pick from the list" Data
> >>Validation in those same cells.
> >>
> >>Now I want to copy those four conditional formats and Data Validation
> >>settings all the way down until the end of the data.
> >>
> >>There is no data in any of these coloumns, but there is data one coloum
> >>to the left.
> >> The number of rows will vary considerably each time this macro is run.
> >>If I were to do this by keyoard I would copy R2:U2,
> >>go to Q2, [end][down][right], then
> >>[shifton][right][right][right][end][up][down][enter]
> >>
> >>I am sure that I can work this movement out in a macro with out too much
> >>problem.
> >>
> >>My biggest problem would be copying and pasting just conditional formats
> >>and Data Validations settings.
> >>
> >>Unless there is an all around easier way to do this?
> >>
> >>This spreadsheet, (all 24 of them) will be coming from Access.
> >>
> >>Phil
> >
> >

--

Dave Peterson