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 :-)

*** Sent via Developersdex http://www.developersdex.com ***

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" wrote in message
...
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 :-)

*** Sent via Developersdex http://www.developersdex.com ***

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 :-)

*** Sent via Developersdex http://www.developersdex.com ***

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)


"Chris Hankin" wrote in message
...
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 :-)

*** Sent via Developersdex http://www.developersdex.com ***

Thanks Chip,

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

Cheers,

Chris.

Live Long and Prosper :-)

*** Sent via Developersdex http://www.developersdex.com ***