Copy Color Format instead of Formula

[Papi]

Is it possible to return color instead of a formula as noted below by using column A which always has data and return color in column G based on all cells in column A?

Sub Test()
    With Sheets("Sheet1")
        .Range("G1:G" & .Cells(.Rows.Count, "A").End(xlUp).Row).Formula = "=Len(A1)"
    End With
End Sub

 

[Rick Rothstein]

Is it possible to return color instead of a formula as noted below by using column A which always has data and return color in column G based on all cells in column A?

It depends... how did the cell in Column G get colored (manually or by Conditional Formatting) and what is the earliest version of Excel that the code will be run in?

 

[Papi]

Hello Rick,

The cell in G would be manually colored (No Conditional Formatting). I believe the lowest version it will go in is 2010.

Thanks

 

[Rick Rothstein]

Hello Rick,

The cell in G would be manually colored (No Conditional Formatting). I believe the lowest version it will go in is 2010.

Okay, the good news is the answer to your original question is "yes"... now, however, you have to tell us exactly what you want the code to do for you (get the color from what cells and do what with it?).

 

[Papi]

Great. The code that would start the section (recorded) is as below. That would put the color in the first data row (2).

Sub Macro2()
    ActiveCell.Offset(1, 0).Range("A1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

 

[Papi]

Next would be to do something similar to the code below except rather than use the "=Len(A1)" to copy the color based on the last code posted in Macro2 and copy that down column G2:G

      .Range("G1:G" & .Cells(.Rows.Count, "A").End(xlUp).Row).Formula = "=Len(A1)"

 

[Rick Rothstein]

Next would be to do something similar to the code below except rather than use the "=Len(A1)" to copy the color based on the last code posted in Macro2 and copy that down column G2:G

      .Range("G1:G" & .Cells(.Rows.Count, "A").End(xlUp).Row).Formula = "=Len(A1)"

Your ultimate question is not entirely clear, but based on the above, I think you may be looking for this...

.Range("G1:G" & .Cells(.Rows.Count, "A").End(xlUp).Row).Interior.Color = vbGreen

 

[Papi]

The code is giving an error at ".Rows" of Compile Error - Invalid or Unqualified Reference. Also, G will change as it adds another column at that point which would make it H1:H and next I1:I each time. Is it possible to have it identify the actual column it is on each time?

 

[Rick Rothstein]

The code is giving an error at ".Rows" of Compile Error - Invalid or Unqualified Reference.

I kept the leading dots because your code line had them. Those leading dots mean they refer to the object of a With statement for the With..End With block they are (supposed to be) in. The error you are reporting appears to mean you tried to execute that line of code without placing it inside a With..End With block so the leading dots had no object (probably a worksheet) to reference.

Also, G will change as it adds another column at that point which would make it H1:H and next I1:I each time. Is it possible to have it identify the actual column it is on each time?

You can change the color of a multiple contiguous columns in the same way you do a single column. What ever your last column is (let's assume it is Column M), just specify it...

.Range("G1:M" & .Cells(.Rows.Count, "A").End(xlUp).Row).Interior.Color = vbGreen

 

[Papi]

Hello Rick. I've been trying to figure this out as I am in over my head. I'm getting the same error code as before. Research says to remove the dot ''.'' in front of Range and add End With etc. statements. Column G is now up to column AL. The existing macro goes to a named cell to the immediate right of the last column beside AL1 called DaysNew. That is how the macro knows where to insert the newest column each time.

I forced in AM2:AM to see what would happen with the same comments about Compile Error - Invalid or Unqualified Reference at ''(.Rows''. Any suggestions as to what I am missing? Thanks again.

Sub Insert_Days()
    Application.Goto Reference:="DaysNew"
    Selection.EntireColumn.Insert
    ActiveCell.Offset(0, -1).Columns("A:A").EntireColumn.Select
    Selection.Copy
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.ClearContents
    Selection.End(xlToLeft).Select
    Selection.Copy
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
'    .Range("AM2:AM" & .Cells(.Rows.Count, "A").End(xlUp).Row).Interior.Color = vbGreen ' Rick Rothstein code
End Sub

I kept the leading dots because your code line had them. Those leading dots mean they refer to the object of a With statement for the With..End With block they are (supposed to be) in. The error you are reporting appears to mean you tried to execute that line of code without placing it inside a With..End With block so the leading dots had no object (probably a worksheet) to reference.



You can change the color of a multiple contiguous columns in the same way you do a single column. What ever your last column is (let's assume it is Column M), just specify it...

.Range("G1:M" & .Cells(.Rows.Count, "A").End(xlUp).Row).Interior.Color = vbGreen