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

LVL 1
csehzIT consultantAsked:
Who is Participating?
 
regmigrantConnect With a Mentor Commented:
or this

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

0
 
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
 
csehzIT consultantAuthor 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
csehzIT consultantAuthor 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
 
csehzIT consultantAuthor Commented:
Never mind thanks that you dealt with the question,

thanks,
Zsolt
0
All Courses

From novice to tech pro — start learning today.