Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

VBA Excel 2010 - Selecting columns

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
csehz
Asked:
csehz
1 Solution
 
Patrick MatthewsCommented:
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
 
csehzAuthor Commented:
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
 
regmigrantCommented:
or this

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

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
csehzAuthor Commented:
Thanks that one I have searched
0
 
andrewssd3Commented:
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
 
andrewssd3Commented:
Sorry should have refreshed before posting
0
 
csehzAuthor Commented:
Never mind thanks that you dealt with the question,

thanks,
Zsolt
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now