PDA

View Full Version : Solved: Code which formats cells



pglufkin
03-18-2013, 09:10 AM
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
03-18-2013, 10:04 AM
instead of setting the color = to the other range's color, try to copy the range col and pastespecial xlPasteFormats into c...

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

pglufkin
03-18-2013, 10:18 AM
Thank you very much, it works, but not near as fast as just copying the colors. Is there any faster code? Thx again.

SamT
03-18-2013, 10:37 AM
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
03-18-2013, 11:01 AM
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
03-18-2013, 11:57 AM
Imagine you didn't have this code, what exactly are you looking for? Please post to show before and after worksheets.

SamT
03-18-2013, 12:06 PM
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
03-18-2013, 01:32 PM
You could try something like this:

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

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

pglufkin
03-29-2013, 12:01 PM
Thank you.

mdmackillop
03-30-2013, 08:34 AM
Another option. This will format a target column to the same as the active cell
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