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.
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.
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.