visual basic.. help

2008-07-11 7:10 am

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

i have tried

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

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.

Can it be done by a VBA code or any other ways?
please kindly advise, thanks =)

更新1:

dont know why i cant see the box 'reply', so i post my reply here after posting ur formula, it seems that nothing happened when the details of the field changed do i need to add anything?

回答 (2)

2008-07-11 7:28 am

✔ 最佳答案

Private Sub Worksheet_Activate()

Cells.Font.ColorIndex = 1
Cells.Font.Bold = False
Cells.Interior.ColorIndex = 2

End Sub

Can I help you?

2008-07-12 14:57:33 補充：
You try this one.

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

Private Sub Worksheet_Activate()
Cells.Font.ColorIndex = 1
Cells.Font.Bold = False
Cells.Interior.ColorIndex = 0
End Sub

2008-07-12 14:57:39 補充：
Actually, I don't really understand your request, so I don't know if this can help you.

2008-07-12 14:59:18 補充：
I've tried. But I don't know if this can reach your request.
If you can, please write again in Chinese.
I think your request is a bit messy.

2008-07-12 16:47:08 補充：
I've tried cecilile's marco.
When I select all and press delete, Excel hanged.
However, mine won't.

👍 0 👎 0

2008-07-12 6:05 am

Try below. This worksheet function should be in the VBA of your particular worksheet. Any change in any cell of the worksheet will trigger below event.

Private Sub Worksheet_Change(ByVal Target As Range)
For Each celldata In Target

celldata.Font.Color = vbWhite
celldata.Interior.Color = RGB(255, 0, 0)

Next

End Sub

👍 0 👎 0

收錄日期: 2021-04-23 23:10:17
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20080710000051KK03445

檢視 Wayback Machine 備份