Work with part of selection VBA

[shippey121]

hi all

started a new role and using excel more and more, no expert in VBA a novice at best but trying to learn. currently stuck on a way to accomplish this so thought id ask.

i want to select and area and format only part of the selection. the code below will format the entire selection, if i selected say A1 to F10 and wanted to colour only the 5th column of the selection, so in this case, E1:E10 green,

 With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
    End With

i tried this but it still format the entire selection

With Selection.Cells(, 5)
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
    End With
End With

doing this

 With Selection.Cells(, 5).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
    End With

formats the 5th column but only on the first row

any help would be appreciated

 

[My Aswer Is This]

Why not just select the area you want to format?

 

[shippey121]

i could do, but the code isnt just to format the area, i already have code that sets the selection as the print area, fits to one page, then exports as PDF and attaches the PDF to an email. i just then type any info i want in the email and press send.

what happens is sometimes i forget which ive emailed and have to look through my sent items to check

certain rows are shaded already so i cant colour the entire selection, im just trying to automate it further

thanks

 

[My Aswer Is This]

Try this:

With Selection.Columns(5).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
    End With

 

[shippey121]

so close.

worked fine.

changed it to

With Selection.Columns(5)
        .Value = Now()
    End With

so it puts time i emailed data

thanks

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

so close.

worked fine.

changed it to

With Selection.Columns(5)
        .Value = Now()
    End With

so it puts time i emailed data

thanks