Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

Copy Green cells over with if statements.

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
RWayneH
Asked:
RWayneH
  • 8
  • 5
  • 2
1 Solution
 
pony10usCommented:
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
 
RWayneHAuthor Commented:
No..  another sub.
0
 
byundtCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
RWayneHAuthor Commented:
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
 
byundtCommented:
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
 
RWayneHAuthor Commented:
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
 
byundtCommented:
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
 
RWayneHAuthor Commented:
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
 
byundtCommented:
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
 
RWayneHAuthor Commented:
Here is a test file, after running this solution.  Please review column AI.   -R-
Sample1.xlsx
0
 
RWayneHAuthor Commented:
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
 
RWayneHAuthor Commented:
However when I do this manually it works fine and replaces the value to what it would be. -R-
0
 
byundtCommented:
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
 
pony10usCommented:
"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
 
RWayneHAuthor Commented:
EXCELent!! -R-
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 8
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now