Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Looking for macro that will auto-fill cells in a range

    Hi all...I am looking for a macro that will auto-fill cells in a range. The attached worksheet lists dates ("Closed") in column A....I want to enter more dates (eg: holidays) when the office is closed. As I do that, the combined effect of formulas in col N and col D cause the word "Closed" to be entered in col D.

    Columns B,C,D are protected; range E8:M88 must remain unprotected and without any formula in those cells. I am looking for some type of macro or 'change sheet' event that will cause "Closed" to be entered in a row (eg: e13:m13) if the date in c13 is entered in col A, (or if it is easier, if the value in d13 = "Closed")......and I can't use conditional formatting to simply shade the row, b/c I have used all 3 cond formatting rules for that range already......any suggestions?
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Does this do what you want?

    Steve
    Code:
    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim lRow As Long
      Dim sClosed As String
      sClosed = "Closed"
      On Error GoTo ErrHandler
      If Not Intersect(Target, Range("A8:A88")) Is Nothing Then
        Application.EnableEvents = False
        Range("E8:M88").Replace What:=sClosed, Replacement:=""
        For lRow = 1 To 88
          If Cells(lRow, 4) = sClosed Then
            Range("E" & lRow & ":M" & lRow).Value = sClosed
          End If
        Next
        Application.EnableEvents = True
      End If
    ExitHandler:
      Application.EnableEvents = True
      Exit Sub
    ErrHandler:
      MsgBox Err.Number & Err.Description
      Resume ExitHandler
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Mar 2010
    Location
    Tampa, FL, USA
    Posts
    114
    Thanks
    11
    Thanked 10 Times in 9 Posts
    (wrong answer)
    Last edited by pjustice57; 2011-05-23 at 18:54. Reason: Answer wrong ... see correct answer above.
    PJ in FL

  4. #4
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi guys...that code seems to work just fine, altho I gather that it is a Worksheet_Change event code....if I already have one of those in the wrlksheet, how can I combine 2 on 1 sheet....see attached file: when I try to combine them, I get an error message when the event code tries to run...??..??
    Attached Files Attached Files

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    how about this?

    I got rid of the disabling the events, since you wanted the changes in E:M to call the event again to change colors (typically I would try to avoid the recursive calls, but neither part is recursive to itself...

    Steve

    Code:
    Option Explicit
    Private Sub Worksheet_Change(ByVal target As Range)
      Dim lRow As Long
      Dim sClosed As String
      Dim oCell As Range
      
      sClosed = "Closed"
      On Error GoTo ErrHandler
      If Not Intersect(target, Range("A8:A88")) Is Nothing Then
        Range("E8:M88").Replace What:=sClosed, Replacement:=""
        For lRow = 1 To 88
          If Cells(lRow, 4) = sClosed Then
            Range("E" & lRow & ":M" & lRow).Value = sClosed
          End If
        Next
      End If
      If Not Intersect(target, Range("e8:m88")) Is Nothing Then
        For Each oCell In Intersect(target, Range("e8:m88")).Cells
          If UCase(oCell) Like "CONF*" Then
            oCell.Interior.ColorIndex = 5
            oCell.Font.ColorIndex = 2
          Else
            Select Case oCell
              Case "Sick Leave"
                oCell.Interior.ColorIndex = 5
                oCell.Font.ColorIndex = 2
              Case "Not at work"
                oCell.Interior.ColorIndex = 4
                oCell.Font.ColorIndex = 1
              Case "Vacation"
                oCell.Interior.ColorIndex = 15
                oCell.Font.ColorIndex = 1
              Case "Closed"
                oCell.Interior.ColorIndex = 15
                oCell.Font.ColorIndex = 1
              Case Else
                oCell.Interior.ColorIndex = xlColorIndexAutomatic
                oCell.Font.ColorIndex = 1
            End Select
          End If
        Next oCell
      End If
    ExitHandler:
      Exit Sub
    ErrHandler:
      MsgBox Err.Number & Err.Description
      Resume ExitHandler
    End Sub

  6. #6
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Steve...I tried your code but it didn't work...I have now combined 2 worksheeet events and it is almost working --- "almost" because, for example, if I enter "Not at work" or "Closed" directly into a cell, it will change colors, but if the value "Closed" is entered via the macro, it doesn't change colors....??...I am attaching the worksheet where I have combined the code....I tried moving the 'formatting' code for "Closed" (Interior.colorindex=15, font.colorindex=1) into the first event code, but it didn't workj properly so I removed it....see attachment
    Attached Files Attached Files

  7. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Steve....my mistake...I don't think I copied all of your code...I tried it again and it seems to be working...let me play with it some more and I will let you know....thank you, as always

Posting Permissions

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