Only update changed cells in variable range triggered by Worksheet_Change

[nl05369]

Hello,
I have a sheet that user can update.
Columns A, C-G and I-J are text
Columns B and H are date format.

I force the format to consolas (to see difference between O and 0), set the date cells to yyyy-mm-dd, do some centering and indenting.
BUT: I do this inefficiently on all cells from row 2 to row 999 because I cannot figure out how to only manipulate the cells that are changed due to multiple formats being used.

What I use now (that works, but leaves me with far to large workbook on save):

[VBA]Sub Worksheet_Change(ByVal target As Range)
On Error Resume Next

If target.Row = 1 Then Exit Sub

Dim Changed_Cell_Address As String
Dim Cell_Range As Range
Changed_Cell_Address = target.Address

Application.EnableEvents = False

Set Cell_Range = Range(Changed_Cell_Address)

'Clear all cell formatting in updated cells
Cell_Range.ClearFormats

' Set cell format to standard Consolas font 9 for easy differentation of numbers
ActiveSheet.Unprotect
ActiveWorkbook.Unprotect
With Range(Changed_Cell_Address).Font
.Name = "Consolas"
.FontStyle = "Regular"
.Size = 9
End With
Range(Changed_Cell_Address).Locked = False
Range("A2", "A999").IndentLevel = 1
Range("B2", "B999").NumberFormat = "yyyy-mm-dd"
Range("B2", "B999").HorizontalAlignment = xlCenter
Range("G2", "G999").HorizontalAlignment = xlCenter
Range("H2", "H999").HorizontalAlignment = xlCenter
Range("I2", "I999").HorizontalAlignment = xlCenter
Application.Run "ThisWorkbook.ExcelDiet"

ActiveSheet.Protect
ActiveWorkbook.Protect
Application.EnableEvents = True

End Sub[/VBA]


how can I manipulate ONLY the changed cells?

 

[James006]

Hello,

The short answer to your question is ... Yes ...

Now ... you do need to be precise and list exactly what needs to be done in each and every case ...

HTH

 

[nl05369]

hi James (or HTH?)..

well, assuming you are asking me to list exactly what needs to be done in every case, then this is the process:

for every altered cell
1: remove all formatting (so no borders, colors etc)
2: change font to CONSOLAS (size 9)

For every >>changed<< cell in column A - .IndentLevel = 1
For every >>changed<< cell in column B - .NumberFormat = "yyyy-mm-dd"
For every >>changed<< cell in column B - .HorizontalAlignment = xlCenter
For every >>changed<< cell in column G - .HorizontalAlignment = xlCenter
For every >>changed<< cell in column H - .NumberFormat = "yyyy-mm-dd"
For every >>changed<< cell in column H - .HorizontalAlignment = xlCenter
For every >>changed<< cell in column I - .HorizontalAlignment = xlCenter

As in my macro above, I do perform these changes, but have only worked out how to do this on ALL the cells in the relevant column from row 1 till 999... which leaved me with a bloated workbook after saving it because far to many empty cells are formatted.
So working out how to do this to only the changed cells per column is stumping me.

cheers
Alex Cotton

 

[James006]

Hello Alex,

You could test following :

Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If Intersect(Target, Range("A:A, B:B, G:G, H:H, I:I")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    With Target
        .ClearFormats
        With .Font
            .Name = "Consolas"
            .FontStyle = "Regular"
            .Size = 9
        End With
      Select Case .Column
            Case 1
                .IndentLevel = 1
            Case 2
                .NumberFormat = "yyyy-mm-dd"
                .HorizontalAlignment = xlCenter
            Case 7
                .HorizontalAlignment = xlCenter
            Case 8
                .NumberFormat = "yyyy-mm-dd"
                .HorizontalAlignment = xlCenter
            Case 9
                .HorizontalAlignment = xlCenter
      End Select
    End With
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = False
End Sub

Hope This will Help - ( HTH )

James

 

[nl05369]

hi James,

my immediate impression: much tidier programming! Also logically set up and easy to read, using nice functions. Thanks!
So I tried this solution, and it almost works.
It stops on doing only 1 case when several rows are pasted at once into the sheet.
I would like to upload the sheet I have (1 tab with the trigger, 1 tab with 4 lines top copy from and paste into the trigger sheet). However, I cannot see how to upload the sheet as my posting permission is "You may not post attachments"

Maybe it is not needed.. I just need to get you code working when multiple lines & columns are pasted in one go.

Thanks for you input so far James (yes, It Helps a Bit

cheers
Alex

 

[James006]

Hi,

You are welcome ...

Indeed, the Worksheet_Change Event macro is designed for User manual input ... one cell at the time ...

If you need to catch a Paste Event ... for several rows and several columns ... it is another question ...

 

[nl05369]

ahh... right. So, in the words of Dr. Lanning in I Robot, "you must ask the right question".
Do I need to start a new thread to perform this to catch a Paste Event, or can I continue here (it is basically the same question, but I am happy to comply with rules it is better to start a new thread).
Thanks in advance James

 

[James006]

As soon as I can get a moment to dig into a test for you ...

I will see if a Loop of each cell in the Target area can fix your problem ... or not ...

By the way would the copy paste process always be blocks of Columns ranging from A to J ... or could it a ' selective ' copy paste without only some columns ...

 

[Fluff]

as long as you are pasting entire rows try

Sub Worksheet_Change(ByVal Target As Range)
   If Intersect(Target, Range("A:B, G:I")) Is Nothing Then Exit Sub
   If Application.CountA(Target) = 0 Then Exit Sub
   Application.EnableEvents = False
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   With Target
      .ClearFormats
      With .Font
         .Name = "Consolas"
         .FontStyle = "Regular"
         .Size = 9
      End With
      .Columns(1).IndentLevel = 1
      With .Columns(2)
         .NumberFormat = "yyyy-mm-dd"
         .HorizontalAlignment = xlCenter
      End With
     .Columns(7).HorizontalAlignment = xlCenter
      With .Columns(8)
         .NumberFormat = "yyyy-mm-dd"
         .HorizontalAlignment = xlCenter
      End With
      .Columns(9).HorizontalAlignment = xlCenter
   End With
   Application.Calculation = xlCalculationAutomatic
   Application.EnableEvents = True
   Application.ScreenUpdating = False
End Sub

 

[nl05369]

This solves it! Even for partial rows it works, because each column always must have the same format. So (re)formatting empty cells is not a problem at all.
I thanks you very much. You have removed a headache for me.
cheers
Alex