asked on

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.

Steven Carnahan

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.

RWayneH

ASKER

No.. another sub.

byundt

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

RWayneH

ASKER

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-

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RWayneH

ASKER

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-

byundt

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

RWayneH

ASKER

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-

byundt

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.

RWayneH

ASKER

Here is a test file, after running this solution. Please review column AI. -R-
Sample1.xlsx

RWayneH

ASKER

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-

RWayneH

ASKER

However when I do this manually it works fine and replaces the value to what it would be. -R-

byundt

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

Steven Carnahan

"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

RWayneH

ASKER

EXCELent!! -R-