use VBA to name a range

Colin Legg · #1 06-20-2011, 05:20 AM

Hi Ken,

I see a few problems with your code.

(1) On the line breaks you are missing some commas.

For example here:

Code:

"A918,A921,A953,A956,A988,A993,A1012,A1015,A1047,A1050,A1082" & _
"A1085,A1117,A1120,A1152,A1156,A1176,A1181,A1200,A1204,A1224,A1228" & _

Should be:

Code:

"A918,A921,A953,A956,A988,A993,A1012,A1015,A1047,A1050,A1082," & _
"A1085,A1117,A1120,A1152,A1156,A1176,A1181,A1200,A1204,A1224,A1228" & _

(2) The compiler won't like this line:

Code:

.Select

You have to give it an object to select. Like this:

Code:

Range(RangeCells).Select

And then you'll need to remove this line if you want all of Range(RangeCells) to be highlighted blue:

Code:

Range("A1").Activate

In fact, there's no need to select any cells here, as I'll show at the end.

(3) When you're using range, if you exceed ~30 non-contiguous cells, you have to use the Union function. Put it all together and I think this should do the trick:

Code:

Sub SetPaleBlue()
    Dim rngCells As Range
    
    Set rngCells = Range("A12,A16,A35,A38,A69,A72,A103,A106,A137,A140")
    Set rngCells = Union(rngCells, Range("A172,A175,A207,A210,A242,A245,A277,A280,A311,A314,A346"))
    Set rngCells = Union(rngCells, Range("A349,A381,A384,A416,A425,A453,A456,A488,A491,A532,A526"))
    Set rngCells = Union(rngCells, Range("A558,A561,A593,A596,A628,A631,A663,A667,A686,A690,A709"))
    Set rngCells = Union(rngCells, Range("A713,A732,A756,A759,A791,A794,A826,A830,A849,A853,A872"))
    Set rngCells = Union(rngCells, Range("A918,A921,A953,A956,A988,A993,A1012,A1015,A1047,A1050,A1082"))
    Set rngCells = Union(rngCells, Range("A1085,A1117,A1120,A1152,A1156,A1176,A1181,A1200,A1204,A1224,A1228"))
    Set rngCells = Union(rngCells, Range("A1248,A1252,A1272,A1276,A1296,A1300,A1320,A1324,A1344,A1348,A1368"))
    Set rngCells = Union(rngCells, Range("A1372,A1392,A1396,A1416,A1420,A1440,A1444,A1464,A1468,A1488,A1492"))
    Set rngCells = Union(rngCells, Range("A1512,A1516,A1536,A1540,A1560,A1564,A1584,A1588,A1608,A1612,A1656"))
    Set rngCells = Union(rngCells, Range("A1660,A1680,A1684,A1704,A1708,A1728,A1732,A1752,A1756,A1776,A1780"))
    Set rngCells = Union(rngCells, Range("A1800,A1804,A1824,A1828,A1848,A1852,A1872,A1876,A1904,A1908,A1939"))
    Set rngCells = Union(rngCells, Range("A1943,A1974,A1977,A2009,A2012,A2044,A2047,A2079,A2082,A2114,A2117"))
    Set rngCells = Union(rngCells, Range("A2149,A2152,A2184,A2187,A2219,A2222,A2254,A2257,A2289,A2292,A2324"))
    Set rngCells = Union(rngCells, Range("A2328,A2347,A2351,A2370,A2374,A2393,A2396,A2428,A2431,A2463,A2466"))
    Set rngCells = Union(rngCells, Range("A2498,A2501,A2533,A2536,A2568,A2571,A2603,A2606,A2638,A2641,A2673"))
    Set rngCells = Union(rngCells, Range("A2677,A2696,A2699,A2731,A2765,A2768,A2800,A2803,A2835,A2838,A2869"))
    Set rngCells = Union(rngCells, Range("A2884,A2887,A2919,A2922,A2941,A2944,A2955,A2958,A2977,A2980"))
    
    With rngCells.Interior
        .ColorIndex = 37
        .Pattern = xlSolid
    End With
    
End Sub

g48dd · #2 06-20-2011, 05:28 AM

Man thank you, I have been fiddling with that a long time, I am reading VBA, but I am just not any where near doing this.

Ken

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Resizing the Name range	derohanes	Excel	4	05-27-2011 03:05 AM
Limit cell range use	talkinglens	Excel	6	12-31-2010 08:43 PM
Find and Replace within range	anil3b2	Word VBA	3	12-01-2010 02:35 AM
Sort Range	boutells	Excel	1	07-15-2009 03:02 AM
Range Formula	aleksandr	Excel	10	05-18-2009 12:14 PM