Geek Answers Handbook

To track style changes in Excel 2007/2010 using VBA

I need to track cell style changes on some sheets. I cannot use buid-in tracking in Excel 2007/2010 because I need to configure something. I tried to track the style change by Workbook_SheetChange, but failed. It never works when I change a cell from one style to another.

Is there any other event that can be used to track style changes? Or any workaround for this?

0
vba excel
source share
1 answer

The event does not fire when the format is changed.

The best workaround is to monitor the Worksheet_SelectionChange event. When a user clicks on a cell, you need to save the link to the cell and all the format information that you want to track. The next time the event fires, you should look back at the last cell they clicked on, compare the current format with the saved format information, and this will allow you to detect the changes.

The downside is that you can only detect changes after they click the button from the cell they formatted.

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static LastRange As Range 'The last range selected' 'For example, monitor the background color or the cell' Static LastColorIndex As Integer If LastRange.Cells(1).Interior.ColorIndex <> LastColorIndex Then 'Do what you do' End If Set LastRange = Target LastColorIndex = Target.Interior.ColorIndex End Sub

This is the simplest possible case. Everything becomes more complicated if they change the entire range of cells at once.

+2
source share

More articles:


All Articles