Solved

Shift grey shaded cells from Column A to Column B

Posted on 2011-09-05
8
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

635 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