Solved

Shift grey shaded cells from Column A to Column B

Posted on 2011-09-05
8
266 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 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

14 Experts available now in Live!

Get 1:1 Help Now