4th Condition (XL2K SP3)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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:

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

Reply | Quote

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

Reply | Quote

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.

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Hi Hans

Excel 2000 and Excel 2003.

Thanks

Braddy

Reply | Quote

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”

Reply | Quote

Hi Hans

Thats just great, Thank you very much.

Braddy

Reply | Quote

Hi Hans

Is it possible to adapt this code that you helped me with, to colour the cell instead of the text.

I apologise if this should have been a new post, I never really sure.

Many Thanks

Braddy

Reply | Quote

Replace all occurrences of Font with Interior

Reply | Quote

Hi Hans

Many thanks for your prompt and most pleasing reply.

Grateful Thanks

Braddy

Reply | Quote

Hi Hans

Just a quick question how can I find a list of the numbers that represent colours as in this code.

Thanks

Braddy

Reply | Quote

See post 444,966 for a workbook that displays the 56 standard colors.

Reply | Quote

Hi Hans

Thank you very much, that will be most useful.

Braddy

Reply | Quote

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:

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

Reply | Quote