Cell Ranges within sheets

[Flue]

HI all.

Can some explain why this code;

Range(Cells(FoundCell.Row, 1), Cells(FoundCell.Row, 6)).Interior.ColorIndex = 6

works for the main sheet, but as soon as you want to assign a sheet name (if working with multiple sheets) to it you have to use;

Sheets("TEST1_15").Range("A" & i & ":F" & i).Interior.ColorIndex = 6

Even this works;

 Sheets("TEST1_15").Range(Cells(FoundCell.Row, 1)).Interior.ColorIndex = 6

But add that second Cell for the range and it fails.

Any Idea?

Thanks.

Brian

 

[rdw72777]

As an FYI, whenever you post code question that use variables but don't post the whole code, the post often goes ignored.

Post the whole code and highlight the line you are talking about, and then people can play with it and see if its a VBA quirk or something in your design (foundCell definition??).

 

[Flue]

Good to know. Thanks.

Sub CompareSheets()
Dim FoundCell, LastCell, FirstAddr
Dim lCount, tCount, i
Application.ScreenUpdating = False

lCount = Sheets("LIVE1_15").UsedRange.Rows.Count
tCount = Sheets("TEST1_15").UsedRange.Rows.Count

With Sheets("LIVE1_15").UsedRange
    Set LastCell = .Cells(.Cells.Count)
End With

For i = 1 To tCount

Set FoundCell = Sheets("LIVE1_15").UsedRange.Find(what:=Sheets("TEST1_15").Cells(i, 2), after:=LastCell)

If Not FoundCell Is Nothing Then
    FirstAddr = FoundCell.Address
End If

Do Until FoundCell Is Nothing
    
    Set FoundCell = Sheets("LIVE1_15").UsedRange.FindNext(after:=FoundCell)
'Highlight matching rows on both sheets  
  Range(Cells(FoundCell.Row, 1), Cells(FoundCell.Row, 6)).Interior.ColorIndex = 6  'This works...If the sheet is not referenced.
    Sheets("TEST1_15").Range("A" & i & ":F" & i).Interior.ColorIndex = 6 'This Works
'But this will not....Sheets("TEST1_15").Range(Cells(i, 1), Cells(i, 5)).Interior.ColorIndex = 6


      
    If FoundCell.Address = FirstAddr Then
        Exit Do
    End If
Loop

Next
End Sub

 

[rdw72777]

Ahh I see. I don't have a certain answer, but I know it traces its roots back to using objects and certain syntax not understanding the object to use. Some of the more experienced coders would be better able to explain it, but basically the non-functioning syntax seeems to not recognize the worksheet (I don't know why). It only seems to know the worksheet it is on, having last referenced the Sheets("LIVE1_15") in the last setting of FoundCell before your errored code.

the quick and dirty way around it is to select the sheet you want with a quick "Sheets("TEST1_15").Select line before you do the formatting and that fixes it.

If you post a question about not using the the Select method (which the top people on this site advise against) and explain the erro situation, you'd probably get the perfect answer. I've always found a way to work around, like you did using Range without the Cells function.

 

[VoG]

Try like this

With Sheets("TEST1_15")
    .Range(.Cells(i, 1), .Cells(i, 5)).Interior.ColorIndex = 6
End With

 

[rdw72777]

Vog, I've noticed sometime even when using the With method that I have to do a .Select line before doing the next action (i.e. changing a format).

What causes this sort of situation, as it usually happens when i toggle bacnk and forth between tabs? I'm not a perfect coder, but does certain syntax solve it? Does Option Explicit solve it (I never use it because I don't know what it is)?

I've tried abolishing .Select from my syntax but sometimes it works when nothing else will.

 

[VoG]

It is almost never necessary to select. In fact the only case that springs to mind with a worksheet is setting Freeze Panes. Can you give an example where you have to select.

Option Explicit is something different - it requires you to declare (Dim) all variables.

 

[rdw72777]

Did you try with the code in the post above. I created a dummy file, re-named 2 sheets as they were in the code provided, and added dummy data in column B of each sheet. I got the same error.

 

[VoG]

No but this worked for me with a different sheet selected

Sub test()
Const i = 5
With Sheets("TEST1_15")
    .Range(.Cells(i, 1), .Cells(i, 5)).Interior.ColorIndex = 6
End With
End Sub

 

[rdw72777]

Right, like I said, there are workarounds to most stuff.

But in his example, this didn't work:

Sheets("TEST1_15").Range(Cells(i, 1), Cells(i, 5)).Interior.ColorIndex = 6

but this did:
Sheets("TEST1_15").select
Sheets("TEST1_15").Range(Cells(i, 1), Cells(i, 5)).Interior.ColorIndex = 6