VBA Moving a cell one cell to the right

[alexfooty]

Hi
I have 21 columns of data C99:W99

I need a macro to move the orange cell (C100) one cell to the right each time the macro is run. When it eventually reaches cell W100 I need it to return to C100
Many thanks

 

[Peter_SSs]

You will have to modify this to get the correct colour of your cell. You can do that by selecting the cell then go to the Fill Color drop-down -> More Colors... -> Custom tab

Sub Move_Cell()
  Dim rFound As Range
  
  Application.FindFormat.Clear
  Application.FindFormat.Interior.Color = RGB(255, 192, 0)
  Set rFound = Rows("100").Find(What:="", LookIn:=xlFormulas2, SearchFormat:=True)
  If Not rFound Is Nothing Then rFound.Cut Destination:=Cells(100, IIf(rFound.Column = 23, 3, rFound.Column + 1))
  Application.FindFormat.Clear
End Sub

 

[alexfooty]

Hi
Thanks for your quick reply. When I run it I get the following...

 

[Peter_SSs]

What is the error message?

It looks like you have not updated the RGB values to match your orange cell which looks quite a different colour to mine.

What Excel version & platform are you using? I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[alexfooty]

Hi Peter
I did update the RGB values to match. I am using Excel 2016, Windows 10
Error...

Please note that I'm 70 years old and VBA illiterate. I can follow some coding but more than 50% is over my head!
Thanks
Alex

 

[alexfooty]

Peter
Sorry - The first cell reference should be E100 and the last cell Y100
My mistake - forgotten I'd changed it. How does this impact the macro?

 

[Peter_SSs]

I did update the RGB values to match.

Does that mean you altered the values in the code or you changed the colour of your cell to match the RGB values I had in the code?


What happens if you change that yellow line to this?

Set rFound = Rows(100).Find(What:="", LookIn:=xlFormulas, SearchFormat:=True)

Please note that I'm 70 years old

No need to worry about that - I'm not very far behind you.

 

[alexfooty]

Set rFound = Rows(100).Find(What:="", LookIn:=xlFormulas, SearchFormat:=True)

Works perfectly with your last tweak. Many thanks for your patience..

Maybe I'm pushing my luck here! - but is it possible to copy a column of figures and paste the values into the column above the orange marker, before moving the marker one cell to the right?

Copy AC81:AC97 & paste values to Col W, then move orange marker along one cell. The next time macro is run copies to Col X and so on.
Sorry to be a pain.
Thanks

 

[Peter_SSs]

The new requirement is not clear to me.

 

[alexfooty]

The values in AC81:AC97 change daily.

Before running the macro that you gave me, I would like to copy AC81:AC97 then always paste the values into the column above the orange cell, whichever position that may be.

Then run the macro you gave me so the orange cell moves one to the right.

Next time the macro is run, the new values are again pasted in the column wherever the orange marker happens to be.

Hope this is clearer.