Selecting a range and changing the colour of the cells

[Finish]

Hello,

Your help much appreciated here. I completely stuck (have been for days).

I have some code below with1) find the last cell in a columnwith data and (2) Attempts having specified the beginning of a range, tries to change the whole range red.

Am gutted, cant do it! Any help mcuh appreciated, here is the code:

Sub Trial()

Dim LastRow As Long
Dim LastCol As Integer
Dim myRange As Range


Workbooks.Open ("C:\Report.xls")


'Find the LAST real row
LastRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row


' Find the LAST real column
LastCol = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column


ActiveWorkbook.Activate

ActiveSheet.Range(Cells(11, 17), _
Cells(LastRow, LastCol)).Interior.Color = vbRed


End Sub

 

[SkipVought]

hi,

ActiveSheet.usedrange.interior.color = vbred

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Finish]

Thanks very much for coming back to me Skip.

However, i only want to highlight the section "bounded" by LastRow and LastCol. When i use what you suggested - it highlights the whole sheet.

but i feel i'm closer.

It all goes wrong with this line:
ActiveSheet.Range(Cells(11, 17), _
Cells(LastRow, LastCol)).Interior.Color = vbRed

It seems that using "Range" with LastRow and LastCol just doesnt work

 

[PHV]

It all goes wrong
What happens ?
Error message ? Unexpected behaviour ? Computer crash ? ... ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

I would guess he (she?) wants to highlight it that way:
[tt]
Col1 Col2 Col3 [red]Col4[/red]
123 234 345 [red]666[/red]
123 234 345 [red]666
123 234 345 666[/red]
[/tt]

Have fun.

---- Andy

 

[SkipVought]

then try this...

ActiveSheet.Cells(11, 17).CurrentRegion.interior.color = vbred

BTW,

     ActiveSheet.Range(Cells(11, 17), _
         Cells(LastRow, LastCol)).Interior.Color = vbRed

would do something like...
[tt]
1
2
...
10
11 [red]col17 col 18
aaa bbb
...
zzz zazaz[/red]
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Finish]

Thanks for the comments Skip, Andy, PH.

My original description wasnt great. ITa s spreasheet with about 16 columns and 6000 rows. but i just want to be able to highlight in red the last column.

Also, the spreadsheet gets potentially updated everyday - whcih is why i need the code to dynamically work out the sieze of that last column.

Not sure if that claries a little (Andy guessed what i was trying to say).

using "CurrentRegion" doesnt work for what i am trying to achieve, but is very useful for the future.Thanks

 

[PHV]

i just want to be able to highlight in red the last column
ActiveSheet.Range(Cells(1, LastCol), Cells(LastRow, LastCol)).Interior.Color = vbRed


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

i just want to be able to highlight in red the last column.

Dim rng As Range
With ActiveSheet.Cells(11, 17).CurrentRegion
    Set rng = ActiveSheet.Columns(.Column + .Columns.Count - 1)
    Intersect(.Cells, rng).Interior.Color = vbRed
End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Finish]

Hi PH,

Still didnt quite work - I get an Application Run time error'1004' Application-defined or object-defined error message

Not sure what i'm doing wrong.

 

[SkipVought]

Did you change the code in ANY way?

If so, post your code.

It runs perfectly!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

And this ?

With ActiveSheet
  .Range(.Cells(1, LastCol), .Cells(LastRow, LastCol)).Interior.Color = vbRed
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

try

Dim rng As Range
With ActiveSheet.Cells(11, 17).CurrentRegion
    Set rng = ActiveSheet.Columns(.Column + .Columns.Count - 1)
    [b]Application.[/b]Intersect(.Cells, rng).Interior.Color = vbRed
End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Finish]

Skip, PH

Yes thanks!! Sorry skip - I think as I posted an error message just you posted a solution.

Both solutions worked - PH a[[liced the red background exactly and suspect Skip, yours will be exact (it just included two more cells above row 11 but is pretty much what i spent 3 days trying to do!)

Many many thanks because i was completely stuck! Will make sure i thoroughly understand what you both did.

Thanks again
Finish