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

 

[Legacy 98055]

Try this untested...

<pre>

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

</pre>

 

[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

 

[Legacy 98055]

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...<pre>
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</pre>

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>

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

<pre/>
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
</pre>

 

[Anthony G.]

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

Anthony