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

Thread: signal change of cell contents and place 1 cell right

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Leicestershire, U K
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    My objective is to be able to place an indicator 1 cell to the right of any cell that a user changes.

    I can use the following but cannot determine what should replace the Target.Value > 100

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column = 1 Then
    ThisRow = Target.Row
    If Target.Value > 100 Then
    Range("B" & ThisRow).Interior.ColorIndex = 3
    Else
    Range("B" & ThisRow).Interior.ColorIndex = xlColorIndexNone
    End If
    End If
    End Sub

    Any suggestions ?
    Thanks

  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

    If all you are looking for is a change then do away with the value condition:

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column = 1 Then
    ThisRow = Target.Row
    Range("B" & ThisRow).Interior.ColorIndex = 3
    Else
    Range("B" & ThisRow).Interior.ColorIndex = xlColorIndexNone
    End If
    End Sub
    Is this right?
    Tom

  3. #3
    Board Regular Bruno's Avatar
    Join Date
    Feb 2002
    Location
    Flanders
    Posts
    484
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Keith, I'm not sure what you are looking for...
    A) put an indicator right off any changed cell : you don't have to check the values

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Cells(Target.Row, Target.Column + 1).Interior.ColorIndex = 3
    End Sub


    B) put an indicator only if the value > 100

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Target.Value > 100 Then
    Cells(Target.Row, Target.Column + 1).Interior.ColorIndex = 3
    Else
    Cells(Target.Row, Target.Column + 1).Interior.ColorIndex = xlColorIndexNone
    End If

    End Sub


  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Leicestershire, U K
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks it works a treat.


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
  •