| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visual Basic programming in Excel for conditional formatting
Hi all,
I am trying to create a spreadsheet that will highlight certain conditions when they match. So far I have what is below. I need to add another case that will highlight the row when a date passes (ie, todays()). I would like it to highlight interior red with white bold font. And how do i get this code to act on multiple rows? Any help appreciated :) Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "L3" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("B3:X3")) Is Nothing Then With Target Select Case .Value Case "condition 1": Range("B3:X3").Interior.ColorIndex = 15 Case "condition 2": Range("B3:X3").Interior.ColorIndex = 35 Case "", "condition3": Range("B3:X3").Interior.ColorIndex = 0 Case "condition 4": Range("B3:X3").Interior.ColorIndex = 0 Case "condition 5": Range("B3:X3").Interior.ColorIndex = 0 End Select End With End If ws_exit: Application.EnableEvents = True End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Visual Basic programming in Excel for conditional formatting
Is this what you mean
Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "L3" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("B3:X3")) Is Nothing Then With Target If IsDate(.Value) Then If .Value < Date Then With Range("B3:X3") .Font.ColorIndex = 2 .Interior.ColorIndex = 3 End With End If Else Select Case .Value Case "condition 1": _ Range("B3:X3").Interior.ColorIndex = 15 Case "condition 2": _ Range("B3:X3").Interior.ColorIndex = 35 Case "", "condition3": _ Range("B3:X3").Interior.ColorIndex = 0 Case "condition 4": _ Range("B3:X3").Interior.ColorIndex = 0 Case "condition 5": _ Range("B3:X3").Interior.ColorIndex = 0 End Select End With End If ws_exit: Application.EnableEvents = True End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Doo0592" wrote in message oups.com... Hi all, I am trying to create a spreadsheet that will highlight certain conditions when they match. So far I have what is below. I need to add another case that will highlight the row when a date passes (ie, todays()). I would like it to highlight interior red with white bold font. And how do i get this code to act on multiple rows? Any help appreciated :) Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "L3" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("B3:X3")) Is Nothing Then With Target Select Case .Value Case "condition 1": Range("B3:X3").Interior.ColorIndex = 15 Case "condition 2": Range("B3:X3").Interior.ColorIndex = 35 Case "", "condition3": Range("B3:X3").Interior.ColorIndex = 0 Case "condition 4": Range("B3:X3").Interior.ColorIndex = 0 Case "condition 5": Range("B3:X3").Interior.ColorIndex = 0 End Select End With End If ws_exit: Application.EnableEvents = True End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Visual Basic programming in Excel for conditional formatting
Nearly, I forgot to mention that the date will be entered in a
different cell from the conditions already set. It will be in M3. Can I set IsDate to a variable that uses this cell as a reference? Or does the date part have to be a separate procedure. Also, I am a nooby at VB but I used to program a little on the BBC and I know basic JavaScript so I understand most of the principles of programming in VB but not all. I almost understand the code you have written but can you explain the .value after IsDate? Is this where I can put my variable? Hope this makes sense! :) Thanks for your help thou, I've been working on this one for ages :( Bob Phillips wrote: Is this what you mean Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "L3" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("B3:X3")) Is Nothing Then With Target If IsDate(.Value) Then If .Value < Date Then With Range("B3:X3") .Font.ColorIndex = 2 .Interior.ColorIndex = 3 End With End If Else Select Case .Value Case "condition 1": _ Range("B3:X3").Interior.ColorIndex = 15 Case "condition 2": _ Range("B3:X3").Interior.ColorIndex = 35 Case "", "condition3": _ Range("B3:X3").Interior.ColorIndex = 0 Case "condition 4": _ Range("B3:X3").Interior.ColorIndex = 0 Case "condition 5": _ Range("B3:X3").Interior.ColorIndex = 0 End Select End With End If ws_exit: Application.EnableEvents = True End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Doo0592" wrote in message oups.com... Hi all, I am trying to create a spreadsheet that will highlight certain conditions when they match. So far I have what is below. I need to add another case that will highlight the row when a date passes (ie, todays()). I would like it to highlight interior red with white bold font. And how do i get this code to act on multiple rows? Any help appreciated :) Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "L3" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("B3:X3")) Is Nothing Then With Target Select Case .Value Case "condition 1": Range("B3:X3").Interior.ColorIndex = 15 Case "condition 2": Range("B3:X3").Interior.ColorIndex = 35 Case "", "condition3": Range("B3:X3").Interior.ColorIndex = 0 Case "condition 4": Range("B3:X3").Interior.ColorIndex = 0 Case "condition 5": Range("B3:X3").Interior.ColorIndex = 0 End Select End With End If ws_exit: Application.EnableEvents = True End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Visual Basic programming in Excel for conditional formatting
I think that the code below is what you want.
As to the .Value, that is just testing the value of the Target cell. If you notice, earlier there is a 'With Target' statement, and so any dot statement (e.g. .Value) will refer to that object. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Address = "$m$3" Then If IsDate(.Value) Then If .Value < Date Then With Range("B3:X3") .Font.ColorIndex = 2 .Interior.ColorIndex = 3 End With End If End If ElseIf .Address = "$$3" Then Select Case .Value Case "condition 1": _ Range("B3:X3").Interior.ColorIndex = 15 Case "condition 2": _ Range("B3:X3").Interior.ColorIndex = 35 Case "", "condition3": _ Range("B3:X3").Interior.ColorIndex = 0 Case "condition 4": _ Range("B3:X3").Interior.ColorIndex = 0 Case "condition 5": _ Range("B3:X3").Interior.ColorIndex = 0 End Select End If End With ws_exit: Application.EnableEvents = True End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Doo0592" wrote in message oups.com... Nearly, I forgot to mention that the date will be entered in a different cell from the conditions already set. It will be in M3. Can I set IsDate to a variable that uses this cell as a reference? Or does the date part have to be a separate procedure. Also, I am a nooby at VB but I used to program a little on the BBC and I know basic JavaScript so I understand most of the principles of programming in VB but not all. I almost understand the code you have written but can you explain the .value after IsDate? Is this where I can put my variable? Hope this makes sense! :) Thanks for your help thou, I've been working on this one for ages :( Bob Phillips wrote: Is this what you mean Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "L3" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("B3:X3")) Is Nothing Then With Target If IsDate(.Value) Then If .Value < Date Then With Range("B3:X3") .Font.ColorIndex = 2 .Interior.ColorIndex = 3 End With End If Else Select Case .Value Case "condition 1": _ Range("B3:X3").Interior.ColorIndex = 15 Case "condition 2": _ Range("B3:X3").Interior.ColorIndex = 35 Case "", "condition3": _ Range("B3:X3").Interior.ColorIndex = 0 Case "condition 4": _ Range("B3:X3").Interior.ColorIndex = 0 Case "condition 5": _ Range("B3:X3").Interior.ColorIndex = 0 End Select End With End If ws_exit: Application.EnableEvents = True End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Doo0592" wrote in message oups.com... Hi all, I am trying to create a spreadsheet that will highlight certain conditions when they match. So far I have what is below. I need to add another case that will highlight the row when a date passes (ie, todays()). I would like it to highlight interior red with white bold font. And how do i get this code to act on multiple rows? Any help appreciated :) Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "L3" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("B3:X3")) Is Nothing Then With Target Select Case .Value Case "condition 1": Range("B3:X3").Interior.ColorIndex = 15 Case "condition 2": Range("B3:X3").Interior.ColorIndex = 35 Case "", "condition3": Range("B3:X3").Interior.ColorIndex = 0 Case "condition 4": Range("B3:X3").Interior.ColorIndex = 0 Case "condition 5": Range("B3:X3").Interior.ColorIndex = 0 End Select End With End If ws_exit: Application.EnableEvents = True End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Visual Basic programming in Excel for conditional formatting
Hi Bob,
This nearly works but i don't think it's treating the date part as a condition to evaluate. As this part runs first the (which is fine as long as it doesn't override the second part) it's turning the cells/ fonts to white and red but they stay that way for the next part. Is there a way we can assign the date value to a vairable to use in the case statement? Thanks Bob Phillips wrote: I think that the code below is what you want. As to the .Value, that is just testing the value of the Target cell. If you notice, earlier there is a 'With Target' statement, and so any dot statement (e.g. .Value) will refer to that object. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Address = "$m$3" Then If IsDate(.Value) Then If .Value < Date Then With Range("B3:X3") .Font.ColorIndex = 2 .Interior.ColorIndex = 3 End With End If End If ElseIf .Address = "$$3" Then Select Case .Value Case "condition 1": _ Range("B3:X3").Interior.ColorIndex = 15 Case "condition 2": _ Range("B3:X3").Interior.ColorIndex = 35 Case "", "condition3": _ Range("B3:X3").Interior.ColorIndex = 0 Case "condition 4": _ Range("B3:X3").Interior.ColorIndex = 0 Case "condition 5": _ Range("B3:X3").Interior.ColorIndex = 0 End Select End If End With ws_exit: Application.EnableEvents = True End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Doo0592" wrote in message oups.com... Nearly, I forgot to mention that the date will be entered in a different cell from the conditions already set. It will be in M3. Can I set IsDate to a variable that uses this cell as a reference? Or does the date part have to be a separate procedure. Also, I am a nooby at VB but I used to program a little on the BBC and I know basic JavaScript so I understand most of the principles of programming in VB but not all. I almost understand the code you have written but can you explain the .value after IsDate? Is this where I can put my variable? Hope this makes sense! :) Thanks for your help thou, I've been working on this one for ages :( Bob Phillips wrote: Is this what you mean Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "L3" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("B3:X3")) Is Nothing Then With Target If IsDate(.Value) Then If .Value < Date Then With Range("B3:X3") .Font.ColorIndex = 2 .Interior.ColorIndex = 3 End With End If Else Select Case .Value Case "condition 1": _ Range("B3:X3").Interior.ColorIndex = 15 Case "condition 2": _ Range("B3:X3").Interior.ColorIndex = 35 Case "", "condition3": _ Range("B3:X3").Interior.ColorIndex = 0 Case "condition 4": _ Range("B3:X3").Interior.ColorIndex = 0 Case "condition 5": _ Range("B3:X3").Interior.ColorIndex = 0 End Select End With End If ws_exit: Application.EnableEvents = True End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Doo0592" wrote in message oups.com... Hi all, I am trying to create a spreadsheet that will highlight certain conditions when they match. So far I have what is below. I need to add another case that will highlight the row when a date passes (ie, todays()). I would like it to highlight interior red with white bold font. And how do i get this code to act on multiple rows? Any help appreciated :) Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "L3" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("B3:X3")) Is Nothing Then With Target Select Case .Value Case "condition 1": Range("B3:X3").Interior.ColorIndex = 15 Case "condition 2": Range("B3:X3").Interior.ColorIndex = 35 Case "", "condition3": Range("B3:X3").Interior.ColorIndex = 0 Case "condition 4": Range("B3:X3").Interior.ColorIndex = 0 Case "condition 5": Range("B3:X3").Interior.ColorIndex = 0 End Select End With End If ws_exit: Application.EnableEvents = True End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Visual Basic programming in Excel for conditional formatting
uh? Don't understand at all.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Doo0592" wrote in message ups.com... Hi Bob, This nearly works but i don't think it's treating the date part as a condition to evaluate. As this part runs first the (which is fine as long as it doesn't override the second part) it's turning the cells/ fonts to white and red but they stay that way for the next part. Is there a way we can assign the date value to a vairable to use in the case statement? Thanks Bob Phillips wrote: I think that the code below is what you want. As to the .Value, that is just testing the value of the Target cell. If you notice, earlier there is a 'With Target' statement, and so any dot statement (e.g. .Value) will refer to that object. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Address = "$m$3" Then If IsDate(.Value) Then If .Value < Date Then With Range("B3:X3") .Font.ColorIndex = 2 .Interior.ColorIndex = 3 End With End If End If ElseIf .Address = "$$3" Then Select Case .Value Case "condition 1": _ Range("B3:X3").Interior.ColorIndex = 15 Case "condition 2": _ Range("B3:X3").Interior.ColorIndex = 35 Case "", "condition3": _ Range("B3:X3").Interior.ColorIndex = 0 Case "condition 4": _ Range("B3:X3").Interior.ColorIndex = 0 Case "condition 5": _ Range("B3:X3").Interior.ColorIndex = 0 End Select End If End With ws_exit: Application.EnableEvents = True End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Doo0592" wrote in message oups.com... Nearly, I forgot to mention that the date will be entered in a different cell from the conditions already set. It will be in M3. Can I set IsDate to a variable that uses this cell as a reference? Or does the date part have to be a separate procedure. Also, I am a nooby at VB but I used to program a little on the BBC and I know basic JavaScript so I understand most of the principles of programming in VB but not all. I almost understand the code you have written but can you explain the .value after IsDate? Is this where I can put my variable? Hope this makes sense! :) Thanks for your help thou, I've been working on this one for ages :( Bob Phillips wrote: Is this what you mean Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "L3" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("B3:X3")) Is Nothing Then With Target If IsDate(.Value) Then If .Value < Date Then With Range("B3:X3") .Font.ColorIndex = 2 .Interior.ColorIndex = 3 End With End If Else Select Case .Value Case "condition 1": _ Range("B3:X3").Interior.ColorIndex = 15 Case "condition 2": _ Range("B3:X3").Interior.ColorIndex = 35 Case "", "condition3": _ Range("B3:X3").Interior.ColorIndex = 0 Case "condition 4": _ Range("B3:X3").Interior.ColorIndex = 0 Case "condition 5": _ Range("B3:X3").Interior.ColorIndex = 0 End Select End With End If ws_exit: Application.EnableEvents = True End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Doo0592" wrote in message oups.com... Hi all, I am trying to create a spreadsheet that will highlight certain conditions when they match. So far I have what is below. I need to add another case that will highlight the row when a date passes (ie, todays()). I would like it to highlight interior red with white bold font. And how do i get this code to act on multiple rows? Any help appreciated :) Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "L3" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("B3:X3")) Is Nothing Then With Target Select Case .Value Case "condition 1": Range("B3:X3").Interior.ColorIndex = 15 Case "condition 2": Range("B3:X3").Interior.ColorIndex = 35 Case "", "condition3": Range("B3:X3").Interior.ColorIndex = 0 Case "condition 4": Range("B3:X3").Interior.ColorIndex = 0 Case "condition 5": Range("B3:X3").Interior.ColorIndex = 0 End Select End With End If ws_exit: Application.EnableEvents = True End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Visual Basic programming in Excel for conditional formatting
Right,
I think I've cracked it! I gave up trying to write code that accounts for all the conditions. Instead I have set Excel's conditional formatting to evaluate for the date <= today (thanks for the code to ignore blanks) and I have used VB to evaluate for the rest. It seems to be working. The only flaw is that if you have entered a true value in M3 and L3 and then delete the value in M3 it clears the formatting for the whole row... because of the ignore blanks! I think I can live with this but if you can think of a way around it I wouldn't say no! lol The last and final thing I need to do is to get the VB code to evaluate for rows 3:500. Can we use a for statement for this and, if so, how do we write it? Ta muchly Bob! :) Doo0592 wrote: Hi Bob, This nearly works but i don't think it's treating the date part as a condition to evaluate. As this part runs first the (which is fine as long as it doesn't override the second part) it's turning the cells/ fonts to white and red but they stay that way for the next part. Is there a way we can assign the date value to a vairable to use in the case statement? Thanks Bob Phillips wrote: I think that the code below is what you want. As to the .Value, that is just testing the value of the Target cell. If you notice, earlier there is a 'With Target' statement, and so any dot statement (e.g. .Value) will refer to that object. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Address = "$m$3" Then If IsDate(.Value) Then If .Value < Date Then With Range("B3:X3") .Font.ColorIndex = 2 .Interior.ColorIndex = 3 End With End If End If ElseIf .Address = "$$3" Then Select Case .Value Case "condition 1": _ Range("B3:X3").Interior.ColorIndex = 15 Case "condition 2": _ Range("B3:X3").Interior.ColorIndex = 35 Case "", "condition3": _ Range("B3:X3").Interior.ColorIndex = 0 Case "condition 4": _ Range("B3:X3").Interior.ColorIndex = 0 Case "condition 5": _ Range("B3:X3").Interior.ColorIndex = 0 End Select End If End With ws_exit: Application.EnableEvents = True End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Doo0592" wrote in message oups.com... Nearly, I forgot to mention that the date will be entered in a different cell from the conditions already set. It will be in M3. Can I set IsDate to a variable that uses this cell as a reference? Or does the date part have to be a separate procedure. Also, I am a nooby at VB but I used to program a little on the BBC and I know basic JavaScript so I understand most of the principles of programming in VB but not all. I almost understand the code you have written but can you explain the .value after IsDate? Is this where I can put my variable? Hope this makes sense! :) Thanks for your help thou, I've been working on this one for ages :( Bob Phillips wrote: Is this what you mean Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "L3" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("B3:X3")) Is Nothing Then With Target If IsDate(.Value) Then If .Value < Date Then With Range("B3:X3") .Font.ColorIndex = 2 .Interior.ColorIndex = 3 End With End If Else Select Case .Value Case "condition 1": _ Range("B3:X3").Interior.ColorIndex = 15 Case "condition 2": _ Range("B3:X3").Interior.ColorIndex = 35 Case "", "condition3": _ Range("B3:X3").Interior.ColorIndex = 0 Case "condition 4": _ Range("B3:X3").Interior.ColorIndex = 0 Case "condition 5": _ Range("B3:X3").Interior.ColorIndex = 0 End Select End With End If ws_exit: Application.EnableEvents = True End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Doo0592" wrote in message oups.com... Hi all, I am trying to create a spreadsheet that will highlight certain conditions when they match. So far I have what is below. I need to add another case that will highlight the row when a date passes (ie, todays()). I would like it to highlight interior red with white bold font. And how do i get this code to act on multiple rows? Any help appreciated :) Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "L3" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("B3:X3")) Is Nothing Then With Target Select Case .Value Case "condition 1": Range("B3:X3").Interior.ColorIndex = 15 Case "condition 2": Range("B3:X3").Interior.ColorIndex = 35 Case "", "condition3": Range("B3:X3").Interior.ColorIndex = 0 Case "condition 4": Range("B3:X3").Interior.ColorIndex = 0 Case "condition 5": Range("B3:X3").Interior.ColorIndex = 0 End Select End With End If ws_exit: Application.EnableEvents = True End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Visual Basic Programming for Excel | New Users to Excel | |||
| Visual Basic programming | Excel Discussion (Misc queries) | |||
| Help with Visual Basic programming | Excel Programming | |||
| visual basic conditional formatting produces strange results | Excel Programming | |||
| visual basic conditional formatting produces strange results | Excel Programming | |||
Hybrid Mode
