Format Cell Colour if contents are Changed ?

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 ?

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

--
Regards,
Tom Ogilvy



"MAS" wrote:

> 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 ?
>
>
>

Thanks Tom, Thats sorted it.


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "MAS" wrote:
>
>> 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 ?
>>
>>
>>