Solved

VBA Excel 2010 - Selecting columns

Posted on 2011-09-16
7
312 Views
Last Modified: 2012-05-12
Dear Experts,

Can you please have a short look on the attached code, in the first row I would like to select data range in column A and also in column E, after changing their colors.

In the current version it works but also changing color for B, C, D column data ranges.

Could you advise how to change it that only the A and E column data range should be changed?

thanks,
Range("A2:A" & LastRowWithValue & "", "E2:E" & LastRowWithValue & "").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 14540253
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

Open in new window

0
Comment
Question by:csehz
7 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36548950
Try this:

With Range("A2:A" & LastRowWithValue)
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 14540253
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
With Range("E2:E" & LastRowWithValue)
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 14540253
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With

Open in new window

0
 
LVL 1

Author Comment

by:csehz
ID: 36548979
Thanks basically I have this version through the macro recorder, just thought that maybe in one row can be done the selection.

Because anyway I have five such columns and looks the code quite long compare to the fact that it is just colouring

0
 
LVL 19

Accepted Solution

by:
regmigrant earned 500 total points
ID: 36549076
or this

I think you have overdone it with the quotes - try:-
Range("A2:A" & LastRowWithValue & ", E2:E" & LastRowWithValue & "").Select

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 1

Author Closing Comment

by:csehz
ID: 36549102
Thanks that one I have searched
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36549117
I think it will also work with subtle shift inyour commas and quotes to specify a non-contiguous range:

Range("A2:A" & LastRowWithValue & ",E2:E" & LastRowWithValue).Select

Open in new window

0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36549130
Sorry should have refreshed before posting
0
 
LVL 1

Author Comment

by:csehz
ID: 36549164
Never mind thanks that you dealt with the question,

thanks,
Zsolt
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
.Range Set 25 77
remove dups 10 37
Excel Question 17 15
Excel - conditional formatting on several columns 9 33
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question