+ Reply to Thread
Results 1 to 3 of 3

Private sub worksheet_change(bval target as range) issue

  1. #1
    Darren
    Guest

    Private sub worksheet_change(bval target as range) issue

    Hi There,

    I have a macro that changes the color of the cell "g4" as the cell changes
    numerical values in this code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Range("g4"), Target) Is Nothing Then
    If Range("g4").Value > 0 Then
    Range("g4").Cells.Interior.ColorIndex = 35
    ElseIf Range("g4").Value < 0 Then
    Range("g4").Cells.Interior.ColorIndex = 38
    Else
    Range("g4").Cells.Interior.ColorIndex = xlNone
    End If
    End If
    End Sub

    I would like to make it so that the color changes as cell values change for
    each cell from "g4" to "g16"

    I figure I can copy and paste this code in the private sub and then adjust
    the cell reference, but I imagine there is a much more simple and efficient
    method that I can't think of.

    Thanks for your help

  2. #2
    Bob Phillips
    Guest

    Re: Private sub worksheet_change(bval target as range) issue

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Range("G4:G16"), Target) Is Nothing Then
    If Target.Value > 0 Then
    Target.Cells.Interior.ColorIndex = 35
    ElseIf Target.Value < 0 Then
    Target.Cells.Interior.ColorIndex = 38
    Else
    Target.Cells.Interior.ColorIndex = xlNone
    End If
    End If
    End Sub


    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "Darren" <[email protected]> wrote in message
    news:[email protected]...
    > Hi There,
    >
    > I have a macro that changes the color of the cell "g4" as the cell changes
    > numerical values in this code:
    >
    > Private Sub Worksheet_Change(ByVal Target As Range)
    > If Not Application.Intersect(Range("g4"), Target) Is Nothing Then
    > If Range("g4").Value > 0 Then
    > Range("g4").Cells.Interior.ColorIndex = 35
    > ElseIf Range("g4").Value < 0 Then
    > Range("g4").Cells.Interior.ColorIndex = 38
    > Else
    > Range("g4").Cells.Interior.ColorIndex = xlNone
    > End If
    > End If
    > End Sub
    >
    > I would like to make it so that the color changes as cell values change

    for
    > each cell from "g4" to "g16"
    >
    > I figure I can copy and paste this code in the private sub and then adjust
    > the cell reference, but I imagine there is a much more simple and

    efficient
    > method that I can't think of.
    >
    > Thanks for your help




  3. #3
    Darren
    Guest

    Re: Private sub worksheet_change(bval target as range) issue

    Thanks much, that works great.

    I knew it should be easy, it's just I am not that skilled yet.

    Thanks again.

    "Bob Phillips" wrote:

    > Private Sub Worksheet_Change(ByVal Target As Range)
    > If Not Application.Intersect(Range("G4:G16"), Target) Is Nothing Then
    > If Target.Value > 0 Then
    > Target.Cells.Interior.ColorIndex = 35
    > ElseIf Target.Value < 0 Then
    > Target.Cells.Interior.ColorIndex = 38
    > Else
    > Target.Cells.Interior.ColorIndex = xlNone
    > End If
    > End If
    > End Sub
    >
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (remove nothere from email address if mailing direct)
    >
    > "Darren" <[email protected]> wrote in message
    > news:[email protected]...
    > > Hi There,
    > >
    > > I have a macro that changes the color of the cell "g4" as the cell changes
    > > numerical values in this code:
    > >
    > > Private Sub Worksheet_Change(ByVal Target As Range)
    > > If Not Application.Intersect(Range("g4"), Target) Is Nothing Then
    > > If Range("g4").Value > 0 Then
    > > Range("g4").Cells.Interior.ColorIndex = 35
    > > ElseIf Range("g4").Value < 0 Then
    > > Range("g4").Cells.Interior.ColorIndex = 38
    > > Else
    > > Range("g4").Cells.Interior.ColorIndex = xlNone
    > > End If
    > > End If
    > > End Sub
    > >
    > > I would like to make it so that the color changes as cell values change

    > for
    > > each cell from "g4" to "g16"
    > >
    > > I figure I can copy and paste this code in the private sub and then adjust
    > > the cell reference, but I imagine there is a much more simple and

    > efficient
    > > method that I can't think of.
    > >
    > > Thanks for your help

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1