Solved

Copy Green cells over with if statements.

Posted on 2013-05-14
15
317 Views
Last Modified: 2013-05-31
Starting in row of E4 of active tab, check and do the following:

using green = Interior.Color = 5296274
using yellow = ColorIndex = 6
using red = Interior.ColorIndex = 3

1.      If yellow leave the row alone and move to the next row down E5 and check that.
2.      If blank leave alone, (there can be multiple blanks in a row),
a.      Look for next value in column E to check
b.      If none there Exit Sub.
3.      If cell in column E is green, and the cell in the same row (Column AJ) is blank.
a.      Copy column C thru I of that row, to AG  out.
b.      If column AJ is not blank.. move to the next row to check in column E.
0
Comment
Question by:RWayneH
  • 8
  • 5
  • 2
15 Comments
 
LVL 26

Expert Comment

by:pony10us
ID: 39165009
Are you using conditional formating to set the cell colors initially?  You could use the same conditions to do the copy.  That would seem to be the easiest option.
0
 

Author Comment

by:RWayneH
ID: 39165235
No..  another sub.
0
 
LVL 80

Expert Comment

by:byundt
ID: 39166934
Here is a macro that does what you have requested.
Sub Shifter()
Dim cel As Range, rgE As Range, rgAJ As Range
With ActiveSheet
    Set rgE = .Range("E4")
    Set rgE = Range(rgE, .Cells(.Rows.Count, rgE.Column).End(xlUp))
    Set rgAJ = Intersect(rgE.EntireRow, .Columns("AJ"))
    For Each cel In rgE.Cells
        If cel.Value = "" Then
        ElseIf cel.Interior.ColorIndex = 6 Then
        ElseIf Intersect(cel.EntireRow, rgAJ) <> "" Then
        ElseIf cel.Interior.Color = 5296274 Then
            Intersect(cel.EntireRow, .Range("AG:AM")).Value = Intersect(cel.EntireRow, .Range("C:I")).Value
        End If
    Next
End With
End Sub

Open in new window

0
 

Author Comment

by:RWayneH
ID: 39168012
This is very close... however then testing I found a few things.

First thing is the green color did not copy over with it, which needs to stay there.

Second is the mappings of the cells copied over did not line up.

Value in column B goes to AI.
Value in column C goes to AG.
Value in column D goes to AH.
Value in column E goes to AJ.
Value in column F goes to AK.
Value in column G goes to AL.
Value in column H is fine and blank
Value in column I goes to AO.

After the copy over the range AG:AO should stay green.  If we could add in the correct mappings when that condition is met, we would be golden.  Thanks. -R-
0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 39168248
Here is the code you requested.

Note that after running the macro, the formerly blank cell in column AJ now has contents. You must clear it before running the macro again if you are making changes during testing.

Your statement about how to handle column H was ambiguous. I interpreted it to mean: "clear the contents of column H"
Sub Shifter()
Dim cel As Range, rgE As Range
With ActiveSheet
    Set rgE = .Range("E4")
    Set rgE = Range(rgE, .Cells(.Rows.Count, rgE.Column).End(xlUp))
End With
For Each cel In rgE.Cells
    With cel.EntireRow
        If cel.Value = "" Then
        ElseIf cel.Interior.ColorIndex = 6 Then
        ElseIf .Range("AJ1").Value <> "" Then           'Test cell from column AJ on same row as cel
        ElseIf cel.Interior.Color = 5296274 Then
            .Range("AG1:AO1").Interior.Color = 5296274
            .Range("AI1").Value = .Range("B1").Value    'Row 1 means same row that cel is on
            .Range("AG1").Value = .Range("C1").Value
            .Range("AH1").Value = .Range("D1").Value
            .Range("AJ1").Value = .Range("E1").Value
            .Range("AK1").Value = .Range("F1").Value
            .Range("AL1").Value = .Range("G1").Value
            .Range("AO1").Value = .Range("I1").Value
            .Range("H1").ClearContents                  'Value in column H is fine and blank
        End If
    End With
Next
End Sub

Open in new window

0
 

Author Comment

by:RWayneH
ID: 39168529
Only remaining issue is in column AI.

Some of the values are coming back as 6.2303E+13 and I need whatever the cell value is in column B to return in AI.

Is there some way to copy exactly what is in B, and not return the 6.2303E+13
?

Please advise and thanks. -R-
0
 
LVL 80

Expert Comment

by:byundt
ID: 39168558
The code is already copying exactly what is in column B.

Are you sure that it was  6.2303E+13 and not  6.2303E-13? The reason I ask is that the second value might be displayed as 0, while the first one is a very large number and not likely to be hidden by formatting.

If in fact the value copied over is  6.2303E-13, then the cure is as simple as formatting column AI using the same number format as column B.

If the above explanation doesn't fit the actual situation, could you please post a workbook that illustrates the problem?

Brad
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:RWayneH
ID: 39168571
they have leading zero's on some and I just copied and pasted one of the values.  I need exactly what was in column B.  -R-
0
 
LVL 80

Expert Comment

by:byundt
ID: 39168940
I'll repeat my statement: the code is already giving you exactly the same value that is in column B.

Does column B contain a formula that might change its result based on what is in columns AG to AO? If so, column AI is going to contain the value in column B before the macro was run.

To resolve this issue, please post a sample workbook illustrating the problem.
0
 

Author Comment

by:RWayneH
ID: 39168983
Here is a test file, after running this solution.  Please review column AI.   -R-
Sample1.xlsx
0
 

Author Comment

by:RWayneH
ID: 39168990
column is formatted as Gerneral...  for some reason it is flipping the value to something other than what is in column B that has a E+13 in it.  -R-
0
 

Author Comment

by:RWayneH
ID: 39168995
However when I do this manually it works fine and replaces the value to what it would be. -R-
0
 
LVL 80

Expert Comment

by:byundt
ID: 39169015
Excel was copying over the value from column B, then converting it from text into a number in a fit of misguided desire to be helpful.

I have thwacked Excel across the backside with the following tweaks to the original macro, and it is now licking its wounds but bringing the data over correctly:
Sub Shifter()
Dim cel As Range, rgE As Range
With ActiveSheet
    Set rgE = .Range("E4")
    Set rgE = Range(rgE, .Cells(.Rows.Count, rgE.Column).End(xlUp))
End With
For Each cel In rgE.Cells
    With cel.EntireRow
        If cel.Value = "" Then
        ElseIf cel.Interior.ColorIndex = 6 Then
        ElseIf .Range("AJ1").Value <> "" Then           'Test cell from column AJ on same row as cel
        ElseIf cel.Interior.Color = 5296274 Then
            .Range("AG1:AO1").Interior.Color = 5296274
            .Range("AI1").NumberFormat = "@"
            .Range("AI1").Value = .Range("B1").Text    'Row 1 means same row that cel is on
            .Range("AG1").Value = .Range("C1").Value
            .Range("AH1").Value = .Range("D1").Value
            .Range("AJ1").Value = .Range("E1").Value
            .Range("AK1").Value = .Range("F1").Value
            .Range("AL1").Value = .Range("G1").Value
            .Range("AO1").Value = .Range("I1").Value
            .Range("H1").ClearContents                  'Value in column H is fine and blank
        End If
    End With
Next
End Sub

Open in new window

0
 
LVL 26

Expert Comment

by:pony10us
ID: 39169128
"Excel was copying over the value from column B, then converting it from text into a number in a fit of misguided desire to be helpful.

I have thwacked Excel across the backside with the following tweaks to the original macro, and it is now licking its wounds but bringing the data over correctly:"


I love it.   LOL
0
 

Author Closing Comment

by:RWayneH
ID: 39211953
EXCELent!! -R-
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

757 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

18 Experts available now in Live!

Get 1:1 Help Now