Solved

Shift grey shaded cells from Column A to Column B

Posted on 2011-09-05
8
265 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:AndreasHermle
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:AndreasHermle
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
Hi andrew:

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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now