Selecting adhoc cells for formatting

[Chris Hankin]

Hello,

Could someone please advise on suitable VBA code that enables me to
select multiple cells in a worksheet (cells that are not necessarily
next to each other) so that I can format them in my code given below?

Sub FormatCellRedandWhite()
'
' Format Cell Red and White Macro
' Macro recorded 16/11/2006 by chris.hankin
' This macro will automatically format the cells background to a red
colour, and
' apply a white coloured font setting.

'
ActiveCell.Select

With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With

With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

End Sub

Any help would be greatly appreciated.

Kind regards,

Chris.

Live Long and Prosper

 

[Chip Pearson]

Try something like

Range("A1,A3,A5,A7,A9").Select



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)

 

[Chris Hankin]

Hello Chip,

Thankyou for your reply.

Using an Excel Range does not really help me. I need to format the
active cells that a user selects on the worksheet. Is there any way of
doing this please?

Kind regards,

Chris.

Live Long and Prosper

 

[Chip Pearson]

I misunderstood your question. You can select non-contiguous ranges of cells
by holding down the CTRL key while you click cells and select ranges. Then
your code will work on those cells.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)

 

[Longbow]

Thanks Chip,

Using the Ctrl key whilst selecting the required cells and then running
the macro works well.

Cheers,

Chris.

Live Long and Prosper