VB Code to format changes in a cell adjustment

[Anthony G.]

Hello to all. James was kind enough to post the following code foe me. Although it does format to my liking, it also highlights the entire range each time a different cell is updated. Any suggestions?

Here's the code:

Private Sub Worksheet_Change(ByVal Update As Range)

Sheets("Sheet1").Unprotect Password:="Anthony"

If Intersect(Update, Range("A1:A5")) Is Nothing Then Exit Sub

With Update
Range("A1:A5").Select

.Font.Bold = True
.Font.Italic = True
With.Interior
.ColorIndex = 38
.Pattern = xlSolid
End With
End With
Sheets("Sheet1").Protect Password:="Anthony"
End Sub

Thanks,

Anthony

[Tom Schreiner]

Try this untested...

Private Sub Worksheet_Change(ByVal Update As Range)

Dim KeepCell As Range

Set KeepCell = ActiveCell

Application.ScreenUpdating = False

Sheets("Sheet1").Unprotect Password:="Anthony"



If Intersect(Update, Range("A1:A5")) Is Nothing Then Exit Sub



With Update

Range("A1:A5").Select



.Font.Bold = True

.Font.Italic = True

With .Interior

.ColorIndex = 38

.Pattern = xlSolid

End With

End With

Sheets("Sheet1").Protect Password:="Anthony"

KeepCell.Activate

Set KeepCell = Nothing

Application.ScreenUpdating = True

End Sub

[Anthony G.]

Thanks for the response Tom, but the entire range continues to highlight upon updating only one cell. Any other suggestions?

Thanks Again,

Anthony

[Tom Schreiner]

What range is defined by "Update"?
I can not test this code on my machine without this info.
Also, what exactly are you doing here?

Thanks,
Tom

[NateO]

Try this untested...

Private Sub Worksheet_Change(ByVal target As Range)

If not application.Intersect(target, Range("A1:A5")) Is Nothing Then

Sheets("Sheet1").Unprotect "Anthony"

With range("a1:a5")

 .Font.Bold = True

 .Font.Italic = True

 .Interior.ColorIndex = 38

End With

Sheets("Sheet1").Protect "Anthony"

end if

End Sub

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-13 20:33 ]

[Anthony G.]

Thanks for the replies, but I'm still not having any luck with the formula. Nate, when I applied your version, the entire range highlights in a specific color.

Tom - I'm responsible for maintaining a workbook that contains 15 worksheets with an enourmous amount of data. Users make adjustments to the file and send it back to me to conduct an analysis. I am looking for a way to track those changes without sharing the workbook.

Any more suggestions?

Thanks a million!!!!

Anthony

[Ivan F Moala]

On 2002-05-15 05:30, Anthony G. wrote:
Thanks for the replies, but I'm still not having any luck with the formula. Nate, when I applied your version, the entire range highlights in a specific color.

Tom - I'm responsible for maintaining a workbook that contains 15 worksheets with an enourmous amount of data. Users make adjustments to the file and send it back to me to conduct an analysis. I am looking for a way to track those changes without sharing the workbook.

Any more suggestions?

Thanks a million!!!!

Anthony

Anthony

Private Sub Worksheet_Change(ByVal Update As Range)



Sheets("Sheet1").Unprotect Password:="Anthony"



If Intersect(Update, Range("A1:A5")) Is Nothing Then Exit Sub



With Update

    .Font.Bold = True

    .Font.Italic = True

    With .Interior

        .ColorIndex = 38

        .Pattern = xlSolid

    End With

End With

Sheets("Sheet1").Protect Password:="Anthony"

End Sub

[Anthony G.]

Ivan it worked!!! Thank you and everone else who took the time to assist me.

Anthony