Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Line in macro to 'Remove Shading' (Excel 97)

    The Macro below deletes the records of a person in my spreadsheet but I need it to also remove the shading of cells that once had data in them in the monthly worksheets.

    The attached a copy of the spreadsheet will demonstrate that after names have been deleted shading still remains.

    This is the macro

    Option Explicit
    Public Sub DeleteName()
    Dim strFName As String, strLName As String
    Dim strPsn As String, strLtion As String
    Dim strSector As String, strEFT As String
    Dim oSheet As Worksheet, rng As Range
    Dim strLFName As String
    Dim iResponse As Integer
    Dim lLastRow As Long
    Dim lRow As Long
    Dim sAccSht As String

    sAccSht = "Accruals"
    If Selection.Parent.Name <> sAccSht Then
    MsgBox "Make your selection from the '" & _
    sAccSht & "' Sheet"
    Exit Sub
    End If
    With Selection.EntireRow
    .Range("a1:G1").Select
    strLName = .Cells(1, 1)
    strFName = .Cells(1, 2)
    strLFName = .Cells(1, 3)
    strPsn = .Cells(1, 4)
    strLtion = .Cells(1, 5)
    strSector = .Cells(1, 6)
    strEFT = .Cells(1, 7)
    End With

    iResponse = MsgBox( _
    prompt:="Are you SURE you want to Delete:" & vbCrLf & vbCrLf & _
    "Name: " & strFName & " " & strLName & vbCrLf & _
    "Position: " & strPsn & vbCrLf & _
    "Location: " & strLtion & vbCrLf & _
    "Sector: " & strSector & " / EFT: " & strEFT, _
    Buttons:=vbYesNo)

    If iResponse <> vbYes Then Exit Sub

    Application.ScreenUpdating = False
    For Each oSheet In Worksheets
    With oSheet
    If .Name <> "Rates" And .Name <> "Instructions" Then
    .Unprotect
    lLastRow = .Range("A65536").End(xlUp).Row
    For lRow = 5 To lLastRow
    If .Cells(lRow, 3) = strLFName Then
    If .Name = "Accruals" Then
    .Range(.Cells(lRow, 1), .Cells(lRow, 2)). _
    ClearContents
    .Range(.Cells(lRow, 4), .Cells(lRow, 10)). _
    ClearContents
    Range(.Cells(lRow, 1), .Cells(lRow, 10)). _
    Interior.ColorIndex = xlNone
    .Cells(lRow, 1).Interior.ColorIndex = 40
    .Cells(lRow, 6).Interior.ColorIndex = 40
    .Cells(lRow, 7).Interior.ColorIndex = 35
    .Cells(lRow, 10).Interior.ColorIndex = 36
    Else
    .Range(.Cells(lRow, 1), .Cells(lRow, 2)). _
    ClearContents
    .Range(.Cells(lRow, 4), .Cells(lRow, 34)). _
    ClearContents
    .Range(.Cells(lRow, 1), .Cells(lRow, 2)). _
    Interior.ColorIndex = xlNone
    .Range(.Cells(lRow, 36), .Cells(lRow, 38)). _
    Interior.ColorIndex = xlNone
    End If
    Set rng = Range(.Range("A5"), .Range("Al1").Offset(lLastRow, 0))
    rng.Sort Key1:=.Range("a1"), Order1:=xlAscending
    Exit For
    End If
    Next
    .Protect
    End If
    End With
    Next oSheet
    SortSheets
    Application.ScreenUpdating = True
    End Sub

  2. #2
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Line in macro to 'Remove Shading' (Excel 97)

    I'm not sure I understand the problem, but I'm guessing that when I delete Nicole Arnell, you want the shaded 7.5s in July to become unshaded.

    It seems to me that in your Else statements, you're clearing the contents of columns 1,2 and 4-34. Then you remove the shading from columns 1,2 and 36-38. I think you just need to add
    .Range(.Cells(lRow, 4), .Cells(lRow, 34)).Interior.ColorIndex=xlnone

    Does that solve the problem??

    BTW, I recently discovered the <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags. If you put these around the macro in your post, it will be much easier to read since it will preserve the tabs that you see in your VBA editor.

  3. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Line in macro to 'Remove Shading' (Excel 97)

    Thankyou very much, this works like a charm.

    Kerry

Posting Permissions

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