highlight range without knowing the range beforehand 
Author Message
 highlight range without knowing the range beforehand

I know I have asked this before but I have lost the answer

I have a macro that formats cells
it finds certain headings that are always at the same place every time then
moves with the active cell then I want it to highlight a range below the
active cell

but this is where I have come unstuck the range changes everytime but there
is a blank cell at the bottom of these ranges all the time
how do I get it to just highlight the cells I need

here is an example of what I have at present but it has the range reference
in but this is what is changing all the time.

Cells.Find(What:="Balance Carried Forward ():", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
        ).Activate

    ActiveCell.Offset(0, 1).Select
    Range("D10:D13").Select
    End With
    Selection.NumberFormat = "$#,##0.00"

Kevin



Fri, 16 Apr 2004 05:43:24 GMT  
 highlight range without knowing the range beforehand
     ActiveCell.Offset(0, 1).Select
     Range(ActiveCell,ActiveCell.End(xldown)).Select
     Selection.NumberFormat = "$#,##0.00"

Regards,
Tom Ogilvy


Quote:
> I know I have asked this before but I have lost the answer

> I have a macro that formats cells
> it finds certain headings that are always at the same place every time
then
> moves with the active cell then I want it to highlight a range below the
> active cell

> but this is where I have come unstuck the range changes everytime but
there
> is a blank cell at the bottom of these ranges all the time
> how do I get it to just highlight the cells I need

> here is an example of what I have at present but it has the range
reference
> in but this is what is changing all the time.

> Cells.Find(What:="Balance Carried Forward ():", After:=ActiveCell,
> LookIn:=xlValues, LookAt:= _
>         xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:=False _
>         ).Activate

>     ActiveCell.Offset(0, 1).Select
>     Range("D10:D13").Select
>     End With
>     Selection.NumberFormat = "$#,##0.00"

> Kevin



Fri, 16 Apr 2004 07:19:45 GMT  
 highlight range without knowing the range beforehand
excelman ?

something like ....

    Cells.Find(What:="search", After:=ActiveCell, _
                LookIn:=xlFormulas, LookAt:=xlPart, _
                SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False).Activate
    Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(1,
0).End(xlDown)).Select

Regards

Trevor


Quote:
> I know I have asked this before but I have lost the answer

> I have a macro that formats cells
> it finds certain headings that are always at the same place every time
then
> moves with the active cell then I want it to highlight a range below the
> active cell

> but this is where I have come unstuck the range changes everytime but
there
> is a blank cell at the bottom of these ranges all the time
> how do I get it to just highlight the cells I need

> here is an example of what I have at present but it has the range
reference
> in but this is what is changing all the time.

> Cells.Find(What:="Balance Carried Forward ():", After:=ActiveCell,
> LookIn:=xlValues, LookAt:= _
>         xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:=False _
>         ).Activate

>     ActiveCell.Offset(0, 1).Select
>     Range("D10:D13").Select
>     End With
>     Selection.NumberFormat = "$#,##0.00"

> Kevin



Fri, 16 Apr 2004 07:28:53 GMT  
 highlight range without knowing the range beforehand
thanks to you both will test this out later

Kevin


Quote:
> excelman ?

> something like ....

>     Cells.Find(What:="search", After:=ActiveCell, _
>                 LookIn:=xlFormulas, LookAt:=xlPart, _
>                 SearchOrder:=xlByRows, SearchDirection:=xlNext, _
>                 MatchCase:=False).Activate
>     Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(1,
> 0).End(xlDown)).Select

> Regards

> Trevor



> > I know I have asked this before but I have lost the answer

> > I have a macro that formats cells
> > it finds certain headings that are always at the same place every time
> then
> > moves with the active cell then I want it to highlight a range below the
> > active cell

> > but this is where I have come unstuck the range changes everytime but
> there
> > is a blank cell at the bottom of these ranges all the time
> > how do I get it to just highlight the cells I need

> > here is an example of what I have at present but it has the range
> reference
> > in but this is what is changing all the time.

> > Cells.Find(What:="Balance Carried Forward ():", After:=ActiveCell,
> > LookIn:=xlValues, LookAt:= _
> >         xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:=False _
> >         ).Activate

> >     ActiveCell.Offset(0, 1).Select
> >     Range("D10:D13").Select
> >     End With
> >     Selection.NumberFormat = "$#,##0.00"

> > Kevin



Sat, 17 Apr 2004 00:44:21 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Macro: Copy from known range/Paste to undetermined range

2. Retrieve Last Cell in a Range but Don't know End of Range

3. Sub Ranges within Known Range?

4. naming ranges without specific range references

5. Q range = excel.intersect(range, range) ?

6. Highlighting a Range on a Userform

7. highlighted range

8. Lookup values in a range and highlight it.

9. Highlighting like names in a range

10. Using VBA to select a highlighted range and paste values to another workbook


 
Powered by phpBB® Forum Software © phpBB Group