EXCEL Macro : Moving cell around using Offset

We can actually move cell around using offset function.
For example, you select and activate cell B2.
Range("B2").Select

If you want to move the cell up to cell B1
ActiveCell.Offset(-1,0).Select

to move the cell to cell B3
ActiveCell.Offset(1,0).Select


to move the cell to cell A2
ActiveCell.Offset(0,-1).Select


to move the cell to cell C2
ActiveCell.Offset(0,1).Select

Below are code that I wrote to play around with offset function.
This code will move the cell around while changing the colour of
the cell

========================================================

'declaring library for sleep function
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub offset_run()

For i = 1 To 4

Range("G16").Select
Range("G16").Interior.Color = RGB(200, 100, 35)
Sleep 500

Range("G16").Select
ActiveCell.Offset(-i, 0).Select
ActiveCell.Interior.Color = RGB(200, 160, 35)
Sleep 500

Range("G16").Select
ActiveCell.Offset(0, -i).Select
ActiveCell.Interior.Color = RGB(200, 160, 35)
Sleep 500

Range("G16").Select
ActiveCell.Offset(i, 0).Select
ActiveCell.Interior.Color = RGB(200, 160, 35)
Sleep 500

Range("G16").Select
ActiveCell.Offset(0, i).Select
ActiveCell.Interior.Color = RGB(200, 160, 35)
Sleep 500

Next i

End Sub

=========================================================
Sub reset()
For i = 1 To 4

Range("G16").Select
Range("G16").Interior.Color = RGB(0, 0, 0)
Sleep 500

Range("G16").Select
ActiveCell.Offset(-i, 0).Select
ActiveCell.Interior.Color = RGB(0, 0, 0)
Sleep 500

Range("G16").Select
ActiveCell.Offset(0, -i).Select
ActiveCell.Interior.Color = RGB(0, 0, 0)
Sleep 500

Range("G16").Select
ActiveCell.Offset(i, 0).Select
ActiveCell.Interior.Color = RGB(0, 0, 0)
Sleep 500

Range("G16").Select
ActiveCell.Offset(0, i).Select
ActiveCell.Interior.Color = RGB(0, 0, 0)
Sleep 500

Next i
End Sub

==========================================================

download example


No comments:

Post a Comment