• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

Shift grey shaded cells from Column A to Column B

Dear Experts:

I would like to achieve the following using VBA

I got numerous grad shaded cells (RGB 222, 222, 222)  in Column A of the active worksheet.

All of these grey shaded cells in Column A should be shifted to the right to Column B.
The non-grey cells should be left untouched.

I have attached a sample  file  for your convenience.

help is much appreciated. Thank you very much in advance.

Regards, Andreas

 Shift-grey-shaded-cells.xlsx
0
AndreasHermle
Asked:
AndreasHermle
  • 4
  • 3
1 Solution
 
MAdSCommented:
Must absolutelly be done by VBA? It can be done manually through very few steps otherwise:
1.Select columns A B C
2.Data > ApplyFilter
3.Click column A filter drop-down button, select "Filter by color" and then select grey box
4.Mark cells selected by the filter and drag them do column B
0
 
AndreasHermleAuthor Commented:
Hi MadS:

thank you very much for your swift answer. I am aware of this alternative approach. Thank you very much.

YES, it has to be done by VBA since the code snippet  will be part of a much larger macro.

Regards, Andreas
0
 
andrewssd3Commented:
Try this:

Public Sub ShiftCells()

    Dim c As Excel.Range
    
    For Each c In Application.Intersect(Range("A:A"), ActiveSheet.UsedRange).Cells
        If c.Interior.Color = RGB(222, 222, 222) Then
            c.Copy c.Offset(0, 1)
            c.Clear
            c.ClearFormats
        End If
    Next c


End Sub

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
AndreasHermleAuthor Commented:
Hi andrew:  

thank you very  much for your swift help. Will get back to you on  this tomorrow morning and let you know. Regards, Andreas
0
 
AndreasHermleAuthor Commented:
Hi andrew:
thank you very much for your quick and professional support.

It works  fine but regrettably not in my specific case. The cells that should get shifted are linked cells, i.e. their values are linked to their source in some other worksheet of the workbook. In these cases it does not work.  In all other cases where the cells shifted are not linked, your macro works well.

I have attached a sample file (with the code integrated) for you to check what I mean.

thank you very much in advance.

Regards, Andreas

 Shift-grey-shaded-cells.xls
0
 
andrewssd3Commented:
You could do this, which copies it as a value, removing the link:

Public Sub ShiftCells()

    Dim c As Excel.Range
    
    For Each c In Application.Intersect(Range("A:A"), ActiveSheet.UsedRange).Cells
        If c.Interior.Color = RGB(222, 222, 222) Then
            c.Value = c.Value
            c.Copy c.Offset(0, 1)
            c.Clear
            c.ClearFormats
        End If
    Next c


End Sub

Open in new window

0
 
andrewssd3Commented:
Or this, which retains the links:
Public Sub ShiftCells()

    Dim c As Excel.Range
    
    For Each c In Application.Intersect(Range("A:A"), ActiveSheet.UsedRange).Cells
        If c.Interior.Color = RGB(222, 222, 222) Then
            c.Cut
            c.Offset(0, 1).Select
            c.Worksheet.Paste
            Application.CutCopyMode = False
        End If
    Next c


End Sub

Open in new window

0
 
AndreasHermleAuthor Commented:
Hi andrew:

great, this did the trick. Thank you very much for your professional help. Regards, Andreas
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now