Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional formattinvia VBA (2003sp2)

    In my office, some people are working on a spreadsheet that they want to look sort of like a Gannt chart, with horizontal lines that change colors to represent different porjcet phases.

    This might be a job for a stacked bar chart of some sort. However, one user is working on a different idea which involves conditional formatting (i.e. changing the background colors ).

    Unfortunately, the conditional formating dialog only allows for three conditions, so only three colors. We need more. He mentiond that you may be able to get more colors conditionaly formatted by using VBA. I agreed, but not being too hep about VBA for Excel, I'm at a lose as to where to start.

    Can someone give me some pointers or a head start?

    Thanks

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

    Re: Conditional formattinvia VBA (2003sp2)

    Conditional formatting provides 3 colors in addition to the color set in Format | Cells. It doesn't make a difference whether you set it manually or using code. But you can use the Worksheet_Change event procedure to set the fill color of a cell to any available color depending on some condition. The code for the Worksheet_Change event goes into the worksheet module; you open this by right-clicking the sheet tab and selecting View Code from the popup menu.
    Here is an example:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("A1:H1")) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, Range("A1:H1")).Cells
    If IsNumeric(oCell) Then
    If oCell > 0 And oCell < 57 Then
    oCell.Offset(2, 0).Interior.ColorIndex = oCell
    Else
    oCell.Offset(2, 0).Interior.ColorIndex = xlColorIndexNone
    End If
    Else
    oCell.Offset(2, 0).Interior.ColorIndex = xlColorIndexNone
    End If
    Next oCell
    Application.EnableEvents = True
    End If
    End Sub

    If you enter a number between 1 and 56 in A1 to H1, the cell two rows below will change color. The attached workbook demonstrates the code. Make sure to enable macros when you open it.

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional formattinvia VBA (2003sp2)

    Thank you very much Hans,

    I apreciate it. Sorry for the lateness of my reply, the web page went away on me last week, then I was out of town.

  4. #4
    Lounger
    Join Date
    Dec 2002
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional formattinvia VBA (2003sp2)

    Hi Hans
    Thank you very much for this macro, it has solved something I have wanted to do for a long time which is to create a sheet that my wife ( a teacher) can easily use to enter student marks and have it automatically grade a mark she enters and keep a running total and grade. It also colours the grades so she can easily see trends. All I need to work out now is how to lock all the cells except the places where she needs to enter names and marks. If I lock them the macro complains ! I think I need to unlock the sheet at the macro start and lock it again at the end.

    Magrat.

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

    Re: Conditional formattinvia VBA (2003sp2)

    If you have Excel 2002 (XP) or later, you can specify that cells may be formatted when you protect the worksheet. If you do so, the macro can color cells. If you don't want the user to be able to format cells, of if you have Excel 2000 or earlier, you must indeed unprotect the sheet at the beginning of the code and protect it again at the end. The above macro would become:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("A1:H1")) Is Nothing Then
    Application.EnableEvents = False
    Me.Unprotect
    For Each oCell In Intersect(Target, Range("A1:H1")).Cells
    If IsNumeric(oCell) Then
    If oCell > 0 And oCell < 57 Then
    oCell.Offset(2, 0).Interior.ColorIndex = oCell
    Else
    oCell.Offset(2, 0).Interior.ColorIndex = xlColorIndexNone
    End If
    Else
    oCell.Offset(2, 0).Interior.ColorIndex = xlColorIndexNone
    End If
    Next oCell
    Me.Protect
    Application.EnableEvents = True
    End If
    End Sub

    You can unlock cells in the Protection tab of Format | Cells...

  6. #6
    Lounger
    Join Date
    Dec 2002
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional formattinvia VBA (2003sp2)

    Many thanks, Hans.

    Sorry I should have said, I'm running Office XP Pro.
    I missed the Format bit in the Protect dialogue - I'll try that. All she needs to enter is the student names and marks I want the sheet to do the rest.

    "Me" "Current Worksheet" ? Didn't know that one.

    I'm just working on the sheet putting border lines around all the cells in a rest of the row as soon as a Name is entered in the first column.

    Thanks again,
    Dennis.

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

    Re: Conditional formattinvia VBA (2003sp2)

    This code is situated in the module belonging to the worksheet. In such a module, Me refers to the worksheet. Similarly, in the ThisWorkbook module, Me refers to the workbook as a whole, and in the module belonging to a userform, Me refers to that form.
    In a standard module (the kind you create by recording a macro or by selecting Insert | Modlule in the Visual Basic Editor), you can't use Me - it doesn't refer to anything.

Posting Permissions

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