Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Active Cell Color Code (Excel 2000)

    Ok, heres one...

    What can I add to this following code to leave current color schema alone in certain cells, like my labels, and only turn other cells Yellow? Do you think protecting my labels with a password would do the trick, or can something be changed in the following code to fix the problem?

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Cells.Interior.ColorIndex = -4142
    With Target.Interior
    .ColorIndex = 6
    End With
    End Sub

    Thank you,
    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Active Cell Color Code (Excel 2000)

    I don't think a password will help. You can define a range to be colored - either explicitly in the code, or by defining a named range using Insert | Name.

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim oRange As Range
    Set oRange = Range("B2:H15")
    oRange.Interior.ColorIndex = -4142
    If Not Intersect(Target, oRange) Is Nothing Then
    Intersect(Target, oRange).Interior.ColorIndex = 6
    End If
    Set oRange = Nothing
    End Sub

    This will only change the fill color for cells in the range B2:H15.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Active Cell Color Code (Excel 2000)

    PERFECT! Thank you Hans...you were right, protecting areas did not help, your additions "did".

    As always, a big salute
    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Active Cell Color Code (Excel 2000)

    Will this also work with multiple ranges? How would I list multiple ranges in this code?
    Thank you
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Active Cell Color Code (Excel 2000)

    You can use either something like

    Set oRange = Range("B2:H15,F20:K25,A3040")

    or like

    Set oRange = Union(Range("B2:H15"), Range("F20:K25"), Range("A3040"))

    You can add more ranges as needed.

Posting Permissions

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