Format Cell Colour if contents are Changed ?

[MAS]

Hi,

I have a multi worksheet workbook with one sheet for each day of the month.

I want to have the background colour of indivdual cells in a range of cells
change IF anyone changes the content of individual cells in that range of
cells.

Any ideas on some VBA to achieve this please ?

 

[Guest]

Alt + F11 to get to the VBE

look in the project explorer for your workbook/project
double click on the thisworkbook entry to bring up the Thisworkbook module

at the top of that module, select Workbook from the left dropdown and Change
from the right dropdown.

this should place the workbook Level change event in the module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

this will fire whenever a cell is edited.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
if not intersect(target,sh.Range("B9:Z20")) is nothing then
target.Interior.colorIndex = 6
end if
End Sub

as an example.

See Chip Pearson's site for an overview of events
http://www.cpearson.com/excel/events.htm

 

[MAS]

Thanks Tom, Thats sorted it.

Alt + F11 to get to the VBE

look in the project explorer for your workbook/project
double click on the thisworkbook entry to bring up the Thisworkbook module

at the top of that module, select Workbook from the left dropdown and
Change
from the right dropdown.

this should place the workbook Level change event in the module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

End Sub

this will fire whenever a cell is edited.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
if not intersect(target,sh.Range("B9:Z20")) is nothing then
target.Interior.colorIndex = 6
end if
End Sub

as an example.

See Chip Pearson's site for an overview of events
http://www.cpearson.com/excel/events.htm