use VBA to name a range

g48dd · #1 06-17-2011, 09:08 AM

Excel 2003: I have a huge range and they are single cells all in Column A but there is no order to them. eg A1,A3,A11,A31,A33,A36,A77 What I want to do is make a macro that names a range and sets that range background color to 37 Pale Blue, but I don't know how to write VBA.

Thank you
Ken

Colin Legg · #2 06-19-2011, 03:22 AM

Hi Ken,

Why don't you turn the macro recorder on and do it manually? A review of the recorded code should show you what the VBA code looks like.

g48dd · #3 06-19-2011, 09:52 AM

Hi, thanks I haven't been back for a day or so busy.... that's the first thing I did, and usually I am OK at tweaking VBA to fit what I need, but I couldn't make it work, what I could do is post what I tweaked and see what I did wrong..... I will try to post tomorrow

Ken

Colin Legg · #4 06-19-2011, 10:34 AM

Sounds good, Ken. We'll try to help.

g48dd · #5 06-20-2011, 02:22 AM

OK this is what I tried to do, since I already have all the ranges for my data down load, I copied part of that code then a a well put in what I thought I needed to make it work. It does not work... I get a compile error.... here is what I have:

Code:

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

    Range("A1").Activate
    With Selection.Interior
        .ColorIndex = 37
        .Pattern = xlSolid
    End With
End Sub

Colin Legg · #6 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 · #7 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

Colin Legg · #8 06-20-2011, 06:34 AM

You're welcome, Ken. Stick with reading up on it - after a while something just clicks and a lot of things begin to make sense. And in the meantime, you can always get some help on here.

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