Solved: Code which formats cells

[pglufkin]

I have the following code:

Sub Worksheet_Calculate()
Dim lR As Long, Col As Range, c As Range
lR = Cells(Rows.Count, Selection.Column).End(xlUp).Row
Application.ScreenUpdating = False
For Each c In Range(Cells(1, Selection.Column), Cells(lR, Selection.Column))
If Not IsEmpty(c) Then
Set Col = c.Offset(0, -c.Offset(0, 1).Value)
c.Interior.Color = Col.Interior.Color
c.Font.Color = Col.Font.Color
End If
Next c
End Sub

It works well, however, instead of copying the color only . . . i would like to copy all the formatting of the cell. Can anyone help with that? thx

[CodeNinja]

instead of setting the color = to the other range's color, try to copy the range col and pastespecial xlPasteFormats into c...

[VBA]Sub Worksheet_Calculate()
Dim lR As Long, Col As Range, c As Range
lR = Cells(Rows.Count, Selection.Column).End(xlUp).Row
Application.ScreenUpdating = False
For Each c In Range(Cells(1, Selection.Column), Cells(lR, Selection.Column))
If Not IsEmpty(c) Then
Set Col = c.Offset(0, -c.Offset(0, 1).Value)
Col.Copy
c.PasteSpecial xlPasteFormats

End If
Next c
End Sub
[/VBA]

[pglufkin]

Thank you very much, it works, but not near as fast as just copying the colors. Is there any faster code? Thx again.

[SamT]

Thank you very much, it works, but not near as fast as just copying the colors.

Well, you are running this every time the sheet is calculated which occurs when anything changes on the sheet or it's formula references.

You are running it on every cell in the column of any selected cell. How many cells in that column are actually changed on the calculate event?

Changing the procedure to a Worksheet_Change event and only running it on the Target range might be possible and would be faster.

[pglufkin]

Thank you. Running it on 10,000 rows and it can pull from any of four columns. But there is only one column that changes when running the procedure (the column which the cursor resides when the procedure is started). How could I just target that column to calculate in the procedure?

[sassora]

Imagine you didn't have this code, what exactly are you looking for? Please post to show before and after worksheets.

[SamT]

How could I just target that column to calculate in the procedure?

That's what it does now, Targets only the selected column. All 10,000 Cells in the Selected Column

[CodeNinja]

You could try something like this:

[VBA]Sub Worksheet_Calculate()
Dim lR As Long, Col As Range, c As Range
lR = Cells(Rows.Count, Selection.Column).End(xlUp).Row
Application.ScreenUpdating = False
Set c = Range(Cells(1, Selection.Column), Cells(lR, Selection.Column))
Set Col = c.Cells(1)
Col.Copy
c.PasteSpecial xlPasteFormats

End Sub[/VBA]

It will take the first cell in the range c and copy the format to all of c...

[pglufkin]

Thank you.

[mdmackillop]

Another option. This will format a target column to the same as the active cell
[VBA]Sub FormatCol()
Dim lR As Long, c As String, cel As Range
Set cel = ActiveCell
cel.Copy
c = InputBox("Column to format")
lR = Cells(Rows.Count, c).End(xlUp).Row
Range(Cells(1, c), Cells(lR, c)).PasteSpecial xlPasteFormats
cel.Select
End Sub
[/VBA]