Reformat changed cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Shawn - 23 Jan 2006 15:20 GMT

I need a VBA code that will cause the formatting of cells A1:A10 to change to
bold, blue if the value of the cell changes.

There are numerical values coded in cells A1:A10 at present. If the user
goes into one of those cells and changes the value to a different value, (say
in Cell A2), I need cell A2 to change its formatting automatically to bold,
blue.

I figure I need a change or change event code.

Signature

Thanks
Shawn

Reply to this Message

Tom Ogilvy - 23 Jan 2006 15:35 GMT

With only 10 cells, it might be just as easy to use conditional formatting.

if cell value is not equal to 3

as a condition for example. Hard code the original numbers in the
condition.

Signature

Regards,
Tom Ogilvy

> I need a VBA code that will cause the formatting of cells A1:A10 to change to
> bold, blue if the value of the cell changes.
[quoted text clipped - 5 lines]
>
> I figure I need a change or change event code.

Reply to this Message

Shawn - 23 Jan 2006 16:09 GMT

I just used those ten cells as an example. It will apply to a much larger
area and is a code I might use over and over in the future. Any help would
be appreciated.

Signature

Thanks
Shawn

> With only 10 cells, it might be just as easy to use conditional formatting.
>
[quoted text clipped - 15 lines]
> >
> > I figure I need a change or change event code.

Reply to this Message

Tom Ogilvy - 23 Jan 2006 16:27 GMT

That means you have to store the starting values of the cells someplace and
make a comparison. Even if the user edits the cell, they don't necessarily
change the value stored there.

the pseudo code might be

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell as Range, rng as Range
if Intersect(Target,Range("A1:a10")) is nothing then exit sub
set rng = Intersect(Target,Range("A1:A10"))
for each cell in rng
if cell.Value <> functionthatfetchesOriginalValue(cell) then
cell.Font.ColorIndex = 5
cell.Font.Bold = True
end if
Next
End Sub

such code would be place in the sheet module for that sheet (right click on
the sheet tab and select view code).

How you want to store those values for reference would be specific to what
you are doing. perhaps on a hidden sheet or in a defined name or something.

for a general reference
Chip Pearson's page on events
http://www.cpearson.com/excel/events.htm

Signature

Regards,
Tom Ogilvy

> I just used those ten cells as an example. It will apply to a much larger
> area and is a code I might use over and over in the future. Any help would
[quoted text clipped - 19 lines]
> > >
> > > I figure I need a change or change event code.

Reply to this Message