Put data in range of selected cells

M

MusicMaker

I use a spreadsheet for duty rostas at work and have macros to enter codes
into a range of selected cells for various things like annual leave (H)
sickness (Y, S or F depending on certificate), maternity leave (M). I
currently merge the selected cells.

Here is an example of my macros.

Sub CodeM()
Application.Run "White"
Application.Run "CellsMerge"
ActiveCell.FormulaR1C1 = "M"
End Sub

Sub White()
ActiveCell.Font.Name = "Arial"
Selection.Font.FontStyle = "Bold"
Selection.Font.Size = 9
ColourCellWhite (0)
NoArrows (0)
End Sub

Sub CellsMerge()
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
End Sub

However, I don't really want the cells merged and would prefer the macro
'CodeM' (and other similar macros I have) to work through each highlighted
cell when the user clicks the button to run it and put the code in each one.

How do I get the macro to move through the selected cells, or put the same
code in all of them.

Many thanks.
 
Z

Zone

You could use something like this:

Sub SetEm()
Dim cell As Range
For Each cell In Selection
SetBold cell
Next cell
End Sub

Sub SetBold(c As Range)
With c
.Font.Bold = True
End With
End Sub
 
M

MusicMaker

Thanks Zone. I adapted your code for my use and it worked a treat.

Many thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top