RWayneH

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.

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.

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.

ASKER

No.. another sub.

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

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-

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

membership

Create an account to see this answer

Signing up is free. No credit card required.

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-

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-

The code is already copying

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

*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

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-

I'll repeat my statement: the code is already giving you

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

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

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

ASKER

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

Sample1.xlsx

Sample1.xlsx

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-

ASKER

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

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

"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

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

ASKER

EXCELent!! -R-