Format changed when the details in cell changed

[angel]

I want when a cell is changed, the font colour will be changed from black
to bold white and the background colour will be changed to red.

I want this function to apply to the whole spreadsheet

Can it be done by a VBA code or any other ways? Please kindly advise.

 

[Stefi]

Try this event sub:
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Font.ColorIndex = 2
Target.Font.Bold = True
Target.Interior.ColorIndex = 3
End Sub
Post if you need help to install it!

Regards,
Stefi


„angel†ezt írta:

 

[angel]

thanks, it works perfect

however, i would like to use it for on-going updates purpose

for instance:
column A B
2.1 10
2.2 11
2.2 12

suppose "10" and "11" are changed to "100", by using your formula, both of
them will change from black to bold white and the background colour will be
changed to red. then i save it.

then when i re-open it next time, i would like everything to be normal again
(eg. both "100" will be the same with all the other fields, NOT in bold
white), and if any field is changed, its format will be changed again as
well. so that it will be very easy to spot which fields have been changed
for each update.

please kindly advise, thanks =)

 

[Gord Dibben]

Add this event code to Thisworkbook module to clear the colors when the workbook
opens.

Private Sub Workbook_Open()
With Sheets("Sheet1") 'edit name to suit
.Cells.Interior.ColorIndex = xlNone
End With
End Sub

Also, Stefi's sheet event code can be changed slightly since the code works only
on one Target at a time.

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
.Font.ColorIndex = 2
.Font.Bold = True
.Interior.ColorIndex = 3
End With
End Sub


Gord Dibben MS Excel MVP

 

[angel]

thanks very much!!
but where is "thisworkbook module"?

 

[Gord Dibben]

Right-click on the Excel Icon left of "File" on the menubar and select "View
Code"

Paste the workbook_open code into that module.

The other code stays in the sheet module.


Gord

 

[angel]

got it, however, when i reopen the file, everything in the format changed box
disappeared, including the format and even contents inside. How can I retain
the contents inside but only the format of them change back to normal?

thanks

 

[Gord Dibben]

That's because the cells font is white due to the change event code and I
neglected to return that to default color.

Private Sub Workbook_Open()
With Sheets("Sheet1").Cells 'edit name to suit
.Interior.ColorIndex = xlNone
.Font.ColorIndex = xlAutomatic
End With
End Sub


Gord

 

[angel]

thanks very much, it works perfect
however, i have some fields in A1 which are the headings.
they also have background colour as well
can i keep them when i re-open the file?

it is because when i re-open the file, all the background colour of the
"format changed fields" as well as the "headings fields" are disappeared.
can i keep those for the "headings fields"?

thank you very much =)

 

[Gord Dibben]

Private Sub Workbook_Open()
Dim rng As Range
For Each rng In Sheets("Sheet1").UsedRange
With rng
If .Interior.ColorIndex = 3 Then
.Interior.ColorIndex = xlNone
.Font.ColorIndex = xlAutomatic
End If
End With
Next
End Sub


Gord