Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Shift grey shaded cells from Column A to Column B

Posted on 2011-09-05
8
Medium Priority
?
284 Views
Last Modified: 2012-05-12
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
Comment
Question by:AndreasHermle
  • 4
  • 3
8 Comments
 
LVL 5

Expert Comment

by:MAdS
ID: 36485692
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
 

Author Comment

by:AndreasHermle
ID: 36485727
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
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36485733
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.

 

Author Comment

by:AndreasHermle
ID: 36485818
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
 

Author Comment

by:AndreasHermle
ID: 36486698
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
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36487055
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
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 2000 total points
ID: 36487091
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
 

Author Closing Comment

by:AndreasHermle
ID: 36493801
Hi andrew:

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question