Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Blue Springs, Missouri, USA
    Posts
    108
    Thanks
    1
    Thanked 0 Times in 0 Posts

    4th Condition (XL2K SP3)

    I have used all of my 3 conditional formatting options but need to add a 4th (maybe a 5th). So I thought I would put something in the ThisWorkbook page so that it would check a range (H8:BI12) to see if the value was an "S" (without the quotes) and then change it to bold green. I am pretty sure that it will have something to do with Application.Intersect but just can't seem to get a handle on it. If anyone could help I would greatly appreicate it.

    Dennis

    <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20>

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

    Re: 4th Condition (XL2K SP3)

    See for example <post#=401842>post 401842</post#> and the first reply in that thread. Post back if you need more help.

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

    Re: 4th Condition (XL2K SP3)

    You will need to replace all of your conditional formatting coditions with code. The code will not be able to overide the formatting set by conditional formatting.

    The code needs to go into the Worksheet Change event routine that is in the module behind the worksheet (not in the ThisWorkbook object). In the VBE right click on the worksheet object in the project explorer and select "View Code" from the pop up menu. Then select Workbook from the left drop down list above the code window. Then you can use some code like the code below:

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Intersect(Target, Range("H8:BI12")) Is Nothing Then Exit Sub
    For Each oCell In Intersect(Target, Range("H8:BI12"))
    Select Case oCell.Value
    Case "S"
    oCell.Font.ColorIndex = 4
    Case "T"
    oCell.Font.ColorIndex = 5
    Case "U"
    oCell.Font.ColorIndex = 6
    Case "V"
    oCell.Font.ColorIndex = 7
    Case Else
    oCell.Font.ColorIndex = 1
    End Select
    Next oCell
    End Sub
    </pre>

    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Blue Springs, Missouri, USA
    Posts
    108
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: 4th Condition (XL2K SP3)

    Hans & Legare...

    Thanx so much for your input. Worked like a champ. Here is the actual code I ended up using.

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Application.EnableEvents = False
    On Error Resume Next
    If Not Application.Intersect(Target, Range("H8:BI12")) Is Nothing Then
    Target(1).Value = UCase(Target(1).Value)
    End If
    Dim oCell As Range
    If Intersect(Target, Range("H8:BI12")) Is Nothing Then Exit Sub
    For Each oCell In Intersect(Target, Range("H8:BI12"))
    Select Case oCell.Value
    Case "S"
    oCell.Font.Bold = True
    oCell.Font.ColorIndex = 10
    End Select
    Next oCell
    Application.EnableEvents = True
    End Sub

    As you can see it forces the value to upper case and colors the font bold green.

    Dennis

    <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20>

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

    Re: 4th Condition (XL2K SP3)

    A couple of comments on your code. First, to do this you should use the Worksheet Change event, NOT the Worksheet Selection Change event. If you use the selection change event, the code will run every time the selection changes, even if no changes were made and that will slow the worksheet down much more than necessary. In addition, I believe that in the Selection Change event, the Target object is the cell that the selection is changing to, not the one it is leaving, and that means that you are not working with the right cell.

    Second, your code only changes the first cell in Target to upper case. That will cause two problems. First, if you paste data into multiple cells in the range H8:BI12, then only the first one will be changed to upper case. Second, if the first cell in the Target range is not in the range H8:BI12, it will still be changed to upper case if any cell in Target is in that range.

    Third, your Select Case has only one condition. That could be done much more easily using conditional formatting. If you have other cases being handled in conditional formatting, then the combination is NOT going to work properly.

    Fourth, you have no Case Else to reset the formatting to a default if none of your conditions are met. This will result in an incorrect color if the cells is changed from "S" to anything else.

    The code to correct the first two thing above is below. I don't know enough about what you are doing to correct the second two.

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    Application.EnableEvents = False
    On Error Resume Next
    If Intersect(Target, Range("H8:BI12")) Is Nothing Then Exit Sub
    For Each oCell In Intersect(Target, Range("H8:BI12"))
    oCell.Value = UCase(oCell.Value)
    Select Case oCell.Value
    Case "S"
    oCell.Font.Bold = True
    oCell.Font.ColorIndex = 10
    End Select
    Next oCell
    Application.EnableEvents = True
    On Error GoTo 0
    End Sub
    </pre>

    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Blue Springs, Missouri, USA
    Posts
    108
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: 4th Condition (XL2K SP3)

    Legare...

    Thanx so much for your input. However... the code DOES work. It forces any lower case entry to capitals throughout the range. Maybe it shouldn't work...but it does. If a person makes an entry in a cell and hits Enter... the entry is forced to upper case. If a person makes an entry and just moves the cursor... the entry stays as lower case until they arrow over the entry and then it is forced to upper case. I have already used up the 3 conditional formattings allowed in the worksheet... that is why I needed the fourth. I have since added a fifth and anticpate more. And you are absolutely correct about the Case Else... I have deliberately left it out until I am sure that I have all the other conditions set... I will then add the case else.

    Again...thanx so much for your help with this.

    Dennis

    <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20>

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: 4th Condition (XL2K SP3)

    Dennis,
    When you press enter on the worksheet, does the selection change or does the cursor stay in the same cell? If it's the latter, then that would explain it because the target cell is the same cell you're already in. That would also explain, as Legare pointed out, why the change does not occur if you manually select a different cell.
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: 4th Condition (XL2K SP3)

    If the workbook has the "Move cursor after enter" option disabled in the Tools/Options dialog, then your code will work in the limited cases that you stated. However, there are many other cases (like the one you stated of changing one of the cells and then selecting another cell with the mouse) where your code is going to fail. Try another test. Enter lower case "s" in several cells not in that range and then copy them and paste them into the range. Only the first cell is going to be changed to upper case. I also think that mixing Conditional formatting with format changes in event routines for the same range is eventually going to cause you some very unexpected results. The two types of formatting are not compatible. If you are happy with the way your code works, leave it that way, but I would be willing to make a small wager that you are eventually going to run into the problems.
    Legare Coleman

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: 4th Condition (XL2K SP3)

    HI Legare

    I have used your code below, with an adaption, which works just fine, however I would like to protect the rest of the sheet except the range P10:X39.
    However if I do I get an error when I delete the contents of one of the cells in the range P10:X39.

    Many Thanks

    Braddy




    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Intersect(Target, Range("P10:X39")) Is Nothing Then Exit Sub
    For Each oCell In Intersect(Target, Range("P10:X39"))
    Select Case oCell.Value
    Case "S"
    oCell.Font.ColorIndex = 9
    Case "C"
    oCell.Font.ColorIndex = 5
    Case "N"
    oCell.Font.ColorIndex = 10
    Case "D"
    oCell.Font.ColorIndex = 7
    Case "T"
    oCell.Font.ColorIndex = 1
    Case Else
    oCell.Font.ColorIndex = 1
    End Select
    Next oCell

    End Sub
    If you are a fool at forty, you will always be a fool

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

    Re: 4th Condition (XL2K SP3)

    Have you unlocked the range P10:X39 in the Protection tab of Format | Cells...?

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: 4th Condition (XL2K SP3)

    Hi Hans

    Yes I have unlocked the range P10:X39

    With no protection it works great but when I protect the rest of the sheet I get an error it stops here Case Else
    oCell.Font.ColorIndex = 1

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: 4th Condition (XL2K SP3)

    Which version(s) of Excel will the workbook be used on?

  13. #13
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: 4th Condition (XL2K SP3)

    Hi Hans

    Excel 2000 and Excel 2003.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  14. #14
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: 4th Condition (XL2K SP3)

    Hi Hans

    Thats just great, Thank you very much.

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: 4th Condition (XL2K SP3)

    In Excel 2003, you can specify that cells can be formatted in a protected worksheet, but in Excel 2000, that is not possible, so you will have to unlock the worksheet before formatting cells, and re-protect it afterwards:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Intersect(Target, Range("P10:X39")) Is Nothing Then Exit Sub
    Me.Unprotect
    For Each oCell In Intersect(Target, Range("P10:X39"))
    Select Case oCell.Value
    Case "S"
    oCell.Font.ColorIndex = 9
    Case "C"
    oCell.Font.ColorIndex = 5
    Case "N"
    oCell.Font.ColorIndex = 10
    Case "D"
    oCell.Font.ColorIndex = 7
    Case Else
    oCell.Font.ColorIndex = 1
    End Select
    Next oCell
    Me.Protect
    End Sub

    If you have protected the worksheet with a password, add it (between quotes) after Me.Unprotect and Me.Protect, e.g.

    Me.Unprotect "Secret"

Page 1 of 2 12 LastLast

Posting Permissions

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