Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: VB Code to format changes in a cell adjustment

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    465
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    465
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Thanks Again,

    Anthony

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    465
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  7. #7
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


    Kind Regards,
    Ivan F Moala From the City of Sails

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Posts
    465
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Anthony

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •