Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formatting (Excel 2000, SP-1)

    This is a another post referencing the same sample workbook that I uploaded in <post#=388686>post 388686</post#> . On both sheets, I use conditional formatting to add rows and shading to the cells based on entries in the sheet. I have several questions.

    1) On the real worksheets, I've applied this conditional formatting to the first 150-500 rows (depending on the workbook). This greatly adds to the file size for potentially no purpose if the user does not add that many items, and it may mean that a user will "run out' of rows if he goes over the arbitary limit I've set. Is there another way to do something like this that is more efficient?

    2) If this is the correct approach to use, how do I keep the "blank" cells from printing? My 30 lines of data in the sample prints out on three pages. I can't set a print area, since I don't know how many rows the user will enter.

    3) Last question (well, I hope so!): on the second sheet in the sample, I had to add another level of conditional formatting to column 8 to control different color highlighting of the dates based on the relationship between planned and forecast/actual dates. Because I need to use all three conditional formatting options to cover all three of my highlight colors, my other formatting had to be replaced. This is fine in the "task-level" rows (once dates are entered), but the "phase-level" items never get formatted correctly (and I don't know ahead of time which rows they will be), and all of the blank cells are, well, blank, instead of formatted with borders. I've tried to figure out a way to apply two formats at once, or apply some formatting directly which then gets taken away by the conditions, but, so far, I haven't been very successful.

    Background and appreciation: I'm not sure if I mentioned in a previous post (I've been asking a lot of Excel questions lately) that although I consider myself a VBA developer (primarily Word and Access), and I hold a Master Instructor's certificate in Office 2000, none of that helps much when I get down and dirty with Excel because I never actually USE Excel for anything! So now that I'm working for a company that DOES use Excel for real work, almost everything I try to do on an actual spreadsheet is something I'm doing for the first time. I have half a dozen advanced Excel books on my shelf, and I'm reading them as time allows, but my need is greatly surpassing my time to study, and my lack of experience hinders my ability to use the books for reference. So I truly appreciate the forum here and the generosity of those more experienced who share their knowledge. Many thanks!

    --Karyl (previously posting as karylm)

  2. #2
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (Excel 2000, SP-1)

    Thanks very much for the code! It works great on the primary sheet. The subsequent sheet, however, only updates when you add additional data to the cells in the other columns which are not fed by Sheet1. It doesn't update when the value of the cells change. This would be okay if it is the only solution, but I was wondering if there a way to activate the Worksheet_Change code when the changes aren't actually changes to what's in the cell, just new values resulting from the formulas already in the cells. (Or is it not working because the sheet isn't active when the change happens?)

    I think the same Worksheet_Change event would also work to add my red/green/yellow highlighting to the date cells, also. I'll work on figuring that out when I'm back at work tomorrow. Again, many thanks!

    --Karyl

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (Excel 2000, SP-1)

    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">Edited by Legare Coleman 7/13/04 to fix a bug in the second VBA routine.</span hi>

    Try removing your conditional formatting and putting the code below in the change event routine for sheet "Primary Data Entry Sheet"

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range, oCell2 As Range
    If Not Intersect(Target, Range("A8:H65535")) Is Nothing Then
    Application.ScreenUpdating = False
    For Each oCell In Intersect(Target, Range("A8:H65535"))
    Range(Range("A1").Offset(oCell.Row - 1, 0), Range("H1").Offset(oCell.Row - 1, 0)).Interior.ColorIndex _
    = xlColorIndexNone
    Range(Range("A1").Offset(oCell.Row - 1, 0), Range("H1").Offset(oCell.Row - 1, 0)).Borders.ColorIndex _
    = xlColorIndexNone
    If Range("A1").Offset(oCell.Row - 1, 0) <> "" Then
    Range(Range("A1").Offset(oCell.Row - 1, 0), Range("H1").Offset(oCell.Row - 1, 0)).Interior.ColorIndex = 36
    End If
    For Each oCell2 In Range(Range("A1").Offset(oCell.Row - 1, 0), Range("H1").Offset(oCell.Row - 1, 0))
    If oCell2.Value <> "" Then
    Range(Range("A1").Offset(oCell.Row - 1, 0), Range("H1").Offset(oCell.Row - 1, 0)).Borders.ColorIndex = 1
    Exit For
    End If
    Next oCell2
    Next oCell
    Application.ScreenUpdating = True
    End If
    End Sub
    </pre>


    and the code below in the change event routine for sheet "Subsequent Sheet"

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range, oCell2 As Range
    If Not Intersect(Target, Range("A8:L65535")) Is Nothing Then
    Application.ScreenUpdating = False
    For Each oCell In Intersect(Target, Range("A8:L65535"))
    Range(Range("A1").Offset(oCell.Row - 1, 0), Range("L1").Offset(oCell.Row - 1, 0)).Interior.ColorIndex _
    = xlColorIndexNone
    Range(Range("A1").Offset(oCell.Row - 1, 0), Range("L1").Offset(oCell.Row - 1, 0)).Borders.ColorIndex _
    = xlColorIndexNone
    If Range("A1").Offset(oCell.Row - 1, 0) <> "" Then
    Range(Range("A1").Offset(oCell.Row - 1, 0), Range("L1").Offset(oCell.Row - 1, 0)).Interior.ColorIndex = 36
    End If
    For Each oCell2 In Range(Range("A1").Offset(oCell.Row - 1, 0), Range("L1").Offset(oCell.Row - 1, 0))
    If oCell2.Value <> "" Then
    Range(Range("A1").Offset(oCell.Row - 1, 0), Range("L1").Offset(oCell.Row - 1, 0)).Borders.ColorIndex = 1
    Exit For
    End If
    Next oCell2
    Next oCell
    Application.ScreenUpdating = True
    End If
    End Sub
    </pre>


    I have attached your sample workbook with the code inserted to show how it works.
    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (Excel 2000, SP-1)

    The cell value change due to the recalculate does not trigger the Worksheet Change event. You can get around this problem bu changing the Worksheet Change event routine for the "Primary Data Entry Sheet" sheet to:

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range, oCell2 As Range
    If Not Intersect(Target, Range("A8:H65535")) Is Nothing Then
    Application.ScreenUpdating = False
    For Each oCell In Intersect(Target, Range("A8:H65535"))
    With Worksheets("Primary Data Entry Sheet")
    Range(Range("A1").Offset(oCell.Row - 1, 0), Range("H1").Offset(oCell.Row - 1, 0)).Interior.ColorIndex _
    = xlColorIndexNone
    Range(Range("A1").Offset(oCell.Row - 1, 0), Range("H1").Offset(oCell.Row - 1, 0)).Borders.ColorIndex _
    = xlColorIndexNone
    If Range("A1").Offset(oCell.Row - 1, 0) <> "" Then
    Range(Range("A1").Offset(oCell.Row - 1, 0), Range("H1").Offset(oCell.Row - 1, 0)).Interior.ColorIndex = 36
    End If
    For Each oCell2 In Range(Range("A1").Offset(oCell.Row - 1, 0), Range("H1").Offset(oCell.Row - 1, 0))
    If oCell2.Value <> "" Then
    Range(Range("A1").Offset(oCell.Row - 1, 0), Range("H1").Offset(oCell.Row - 1, 0)).Borders.ColorIndex = 1
    Exit For
    End If
    Next oCell2
    End With
    With Worksheets("Subsequent Sheet")
    .Range(.Range("A1").Offset(oCell.Row - 1, 0), .Range("L1").Offset(oCell.Row - 1, 0)).Interior.ColorIndex _
    = xlColorIndexNone
    .Range(.Range("A1").Offset(oCell.Row - 1, 0), .Range("L1").Offset(oCell.Row - 1, 0)).Borders.ColorIndex _
    = xlColorIndexNone
    If .Range("A1").Offset(oCell.Row - 1, 0) <> "" Then
    .Range(.Range("A1").Offset(oCell.Row - 1, 0), .Range("L1").Offset(oCell.Row - 1, 0)).Interior.ColorIndex = 36
    End If
    For Each oCell2 In .Range(.Range("A1").Offset(oCell.Row - 1, 0), .Range("L1").Offset(oCell.Row - 1, 0))
    If oCell2.Value <> "" Then
    .Range(.Range("A1").Offset(oCell.Row - 1, 0), .Range("L1").Offset(oCell.Row - 1, 0)).Borders.ColorIndex = 1
    Exit For
    End If
    Next oCell2
    End With
    Next oCell
    Application.ScreenUpdating = True
    End If
    End Sub
    </pre>


    Please see the correction I made to the Worksheet Change event routine in my previous post. This code should stay there.
    Legare Coleman

Posting Permissions

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