Try this instead where the only selection is the range to change.
Change interior color index number to suit.

Sub chnagefrombuttonmultiple()
Dim c As Range
For Each c In Selection ' or range("a2:a6") for NO selection
If IsNumeric(c) Then
c.Formula = "=offset(lan!a1," & c.value & ",b1)"
c.Interior.ColorIndex = 3
End If
Next c
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sjaakve" wrote in message
...
works beautifully,

thanx.

I made some changes of my own. Took me all day to do that. posted
below.

Sub Lan()
Dim offset As Integer
For Each c In Selection
c.Select
ActiveCell.Select
If IsNumeric(ActiveCell.Value) Then
offset = ActiveCell.Value
ActiveCell = "=PROPER(OFFSET(LAN!A1," & offset & ",B1))"
With Selection.Interior
.Color = 5296274
End With
End If
Next c
End Sub

Now the macro work when multiple cells are selected, and when selected
cells are merged.

Hope you like it.

grts,

Sjaak