Ignore range within a range?

[dropkickweasel]

Hi,

Is there a way to select a range of cells, without selecting some of the cells within that range?

I have a 14x14 range of cells (B2:O15) as a named range "Outside1".
Within that is a 12x12 range of cells (C3:N14) as a named range "Inside1".

(There will be several similar ranges named "OutsideN" and "InsideN" once I get the code working properly. Currently I have an Outside2 and an Inside2, but nothing further).

What I want to happen is once N is defined (currently just writing a 1 or a 2 in cell A1, but eventually will be the outcome of an option button on a userform), the cells that are in OutsideN are filled light blue, but the cells that are in InsideN are left untouched.

Initially, I had a code that changed all of OutsideN to light blue, then removed the fill from InsideN.
This isn't an adequate solution as some of the cells in InsideN will have their own colour fill and setting this is also removed with this solution.

My current solution is to copy all of InsideN to a temporary range ("Temp"), change all of OutsideN to light blue, then paste back from Temp to InsideN.
This works fine, but relies on a 12x12 'helper range'.

Here's the code that I am currently using. I am always open to suggestions for better practice and cleaner coding:

Sub Test()

Application.ScreenUpdating = False

Dim i As Long
i = Sheets("Sheet1").Range("a1")

    Sheets("Sheet1").Range("Inside" & i).Select
    Selection.Copy
    Sheets("Sheet1").Range("Temp").Select
    ActiveSheet.Paste
    
    Sheets("Sheet1").Range("Outside" & i).Interior.Color = 16247773

    Sheets("Sheet1").Range("Temp").Select
    Selection.Copy
    Sheets("Sheet1").Range("Inside" & i).Select
    ActiveSheet.Paste

Application.CutCopyMode = False
Sheets("Sheet1").Range("a1").Select
Application.ScreenUpdating = True

End Sub

Does anyone have a suggestion for a more efficient method of achieving the same outcome?

To think of it another way, how can I change the colour of the cells in the first and last row and column of a range, without affecting the other cells in that range?

 

[Proulxs]

I tend to be lazy when I write code and would simply do multiples ranges; Sheets("Sheet1").Range("B2:O2,B3:B14,O3:O14,B15:O15").Interior.Color = 16247773

Is there a specific reason to avoid drawing 4 ranges around the inner?

 

[dropkickweasel]

Hi *Proulxs,

That thought had occured to me as well.

The reason I wanted to explore other solutions first was that I will eventually have ~30 different "Outside" ranges. (The inside ranges are used for other things so they exist anyway).
I thought that having 30 ranges and seeing if there was a clever code solution to ignore the Inside ranges would be more efficient / lazier than creating ~120 ranges.

The way to get around that would be some clever way of generating the four 'edge' ranges for the 'Outside' based on the 'Inside' ranges that already exist, but I wouldn't know how to go about doing that either...

 

[Proulxs]

I belive this is what you look for: Non-Intersect Range VBA

This will make a function that use Union to add the cells that does not intersect in the two ranges to a newly created range. You can then call that function in your main code to create the range when you need it.

 

[Snakehips]

@dropkickweasel Are you just wanting to colour the outer cells?

If so then maybe

Sub Shade_Outside()
Application.ScreenUpdating = False
Dim i As Long
Dim Lr As Long
Dim Lc As Long

i = Sheets("Sheet1").Range("a1")

 Lr = Sheets("Sheet1").Range("Outside" & i).Rows.Count
 Lc = Sheets("Sheet1").Range("Outside" & i).Columns.Count
 
    Sheets("Sheet1").Range("Outside" & i).Rows(1).Interior.Color = 16247773
    Sheets("Sheet1").Range("Outside" & i).Rows(Lr).Interior.Color = 16247773
    Sheets("Sheet1").Range("Outside" & i).Columns(1).Interior.Color = 16247773
    Sheets("Sheet1").Range("Outside" & i).Columns(Lc).Interior.Color = 16247773

Application.ScreenUpdating = True
End Sub

 

[Snakehips]

In case it gives you any food for thought, here is same but different.

Sub Shade_Outside2()

Application.ScreenUpdating = False
Dim i As Long
Dim Lr As Long
Dim Lc As Long
Dim ROut As Range

i = Sheets("Sheet1").Range("a1")

Set RFull = Sheets("Sheet1").Range("Outside" & i)
With RFull
    
    Set ROut = Union(.Rows(1), .Rows(.Rows.Count), .Columns(1), .Columns(.Columns.Count))
    ROut.Interior.Color = 16247773
End With
 
Application.ScreenUpdating = True

End Sub

 

[dropkickweasel]

*proulxs,

I had a look at the link you provided and I shall have to look at it again in the morning when it might make more sense to me. Always appreciate the opportunity for greater understanding though, so thank you :]

Snakehips,

You are quite right - what I'm trying to do is colour the outer cells.

I always forget that you can use 'count' to effectively return the last row/column in a range, so that's a nice reminder for me and the first solution makes perfect sense to me.

I feel like the second solution is possibly more efficient, but there's one thing I'd like to ask - you define Lr and Lc at the start, but don't use them at any point... is this a copy/paste oversight and Lr/Lc aren't needed, or is it possible to set Lr to replace '.Rows(.Rows.Count)' with '.Rows(Lr)'?

 

[Snakehips]

@dropkickweasel Yes. In the second code Lr and Lc are not used. Their declaration was a remnant of that first trial code and the two Dim statements can be removed.