| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help modifying VB script
I am working on a script to change the background color of a cell when it is
double-clicked. So far, I have this script, which works fine..... Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) If Target.Interior.ColorIndex = 50 Then Target.Interior.ColorIndex = xlNone Else Target.Interior.ColorIndex = 50 End If Cancel = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) End Sub I am trying to modify this so that the target color changes based on the cell value. For instance, this is a grading sheet, and I'd like all scores of 1 or 2 to change to red, while 3 and 4 change to green. I've tried using an IF THEN statement around the above code to look at the value of the cell but it isn't working, likely because of the coding. I wrote: IF Target.Value "2" Then...(original code) Else...(Original code with different color value) I am getting an error running this, and am hoping someone can tell me why. I haven't programmed script in a long time, so I'm hoping it's a simple fix. Thanks! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help modifying VB script
It would be useful if you posted your actual code rather than an outline of
it (kind of hard to tell where your mistake is if we can't see your actual code) and, if you are getting an error, telling us what error it is (kind of hard to know what VB is complaining about if we can't see what it is saying). Rick "DTLay" wrote in message ... I am working on a script to change the background color of a cell when it is double-clicked. So far, I have this script, which works fine..... Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) If Target.Interior.ColorIndex = 50 Then Target.Interior.ColorIndex = xlNone Else Target.Interior.ColorIndex = 50 End If Cancel = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) End Sub I am trying to modify this so that the target color changes based on the cell value. For instance, this is a grading sheet, and I'd like all scores of 1 or 2 to change to red, while 3 and 4 change to green. I've tried using an IF THEN statement around the above code to look at the value of the cell but it isn't working, likely because of the coding. I wrote: IF Target.Value "2" Then...(original code) Else...(Original code with different color value) I am getting an error running this, and am hoping someone can tell me why. I haven't programmed script in a long time, so I'm hoping it's a simple fix. Thanks! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help modifying VB script
hi
this should work Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) If Target.Value = "" Then Target.Interior.ColorIndex = xlnone Else If Target.Value = 1 Or Target.Value = 2 Then Target.Interior.ColorIndex = 3 Else If Target.Value = 3 Or Target.Value = 4 Then Target.Interior.ColorIndex = 4 End If End If End If End Sub however, you may find it easier to use conditional formating. highlight your range to color change. on the menu bar.... formatsconditional formating when the conditional formating box come up...2 conditions. if cell value is......less than or equal to......2 format to red click the add button if cell vaue is......greater than or equal to.....3 format to green. the cell will change color as you enter or change the data. no code needed. Regards FSt1 "DTLay" wrote: I am working on a script to change the background color of a cell when it is double-clicked. So far, I have this script, which works fine..... Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) If Target.Interior.ColorIndex = 50 Then Target.Interior.ColorIndex = xlNone Else Target.Interior.ColorIndex = 50 End If Cancel = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) End Sub I am trying to modify this so that the target color changes based on the cell value. For instance, this is a grading sheet, and I'd like all scores of 1 or 2 to change to red, while 3 and 4 change to green. I've tried using an IF THEN statement around the above code to look at the value of the cell but it isn't working, likely because of the coding. I wrote: IF Target.Value "2" Then...(original code) Else...(Original code with different color value) I am getting an error running this, and am hoping someone can tell me why. I haven't programmed script in a long time, so I'm hoping it's a simple fix. Thanks! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help modifying VB script
hi
found a bug in conditional formating. need 3 conditons 1. if cell value is....equal to.......0 no formating click the add button 2. if cell value is......less than or equal to......2 format to red click the add button 3. If cell vaue is......greater than or equal to.....3 format to green sorry about that. regards FSt1 "FSt1" wrote: hi this should work Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) If Target.Value = "" Then Target.Interior.ColorIndex = xlnone Else If Target.Value = 1 Or Target.Value = 2 Then Target.Interior.ColorIndex = 3 Else If Target.Value = 3 Or Target.Value = 4 Then Target.Interior.ColorIndex = 4 End If End If End If End Sub however, you may find it easier to use conditional formating. highlight your range to color change. on the menu bar.... formatsconditional formating when the conditional formating box come up...2 conditions. if cell value is......less than or equal to......2 format to red click the add button if cell vaue is......greater than or equal to.....3 format to green. the cell will change color as you enter or change the data. no code needed. Regards FSt1 "DTLay" wrote: I am working on a script to change the background color of a cell when it is double-clicked. So far, I have this script, which works fine..... Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) If Target.Interior.ColorIndex = 50 Then Target.Interior.ColorIndex = xlNone Else Target.Interior.ColorIndex = 50 End If Cancel = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) End Sub I am trying to modify this so that the target color changes based on the cell value. For instance, this is a grading sheet, and I'd like all scores of 1 or 2 to change to red, while 3 and 4 change to green. I've tried using an IF THEN statement around the above code to look at the value of the cell but it isn't working, likely because of the coding. I wrote: IF Target.Value "2" Then...(original code) Else...(Original code with different color value) I am getting an error running this, and am hoping someone can tell me why. I haven't programmed script in a long time, so I'm hoping it's a simple fix. Thanks! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help modifying VB script
If your BeforeDoubleClick event code answer the OP's question, then this
slightly simplified code should also work... Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next Target.Interior.ColorIndex = Choose(Val(Target.Value), 3, 3, 4, 4) End Sub Rick "FSt1" wrote in message ... hi this should work Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) If Target.Value = "" Then Target.Interior.ColorIndex = xlnone Else If Target.Value = 1 Or Target.Value = 2 Then Target.Interior.ColorIndex = 3 Else If Target.Value = 3 Or Target.Value = 4 Then Target.Interior.ColorIndex = 4 End If End If End If End Sub however, you may find it easier to use conditional formating. highlight your range to color change. on the menu bar.... formatsconditional formating when the conditional formating box come up...2 conditions. if cell value is......less than or equal to......2 format to red click the add button if cell vaue is......greater than or equal to.....3 format to green. the cell will change color as you enter or change the data. no code needed. Regards FSt1 "DTLay" wrote: I am working on a script to change the background color of a cell when it is double-clicked. So far, I have this script, which works fine..... Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) If Target.Interior.ColorIndex = 50 Then Target.Interior.ColorIndex = xlNone Else Target.Interior.ColorIndex = 50 End If Cancel = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) End Sub I am trying to modify this so that the target color changes based on the cell value. For instance, this is a grading sheet, and I'd like all scores of 1 or 2 to change to red, while 3 and 4 change to green. I've tried using an IF THEN statement around the above code to look at the value of the cell but it isn't working, likely because of the coding. I wrote: IF Target.Value "2" Then...(original code) Else...(Original code with different color value) I am getting an error running this, and am hoping someone can tell me why. I haven't programmed script in a long time, so I'm hoping it's a simple fix. Thanks! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help modifying VB script
Of course, it would have been nice if I had used the same procedure header
that you did... Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) On Error Resume Next Target.Interior.ColorIndex = Choose(Val(Target.Value), 3, 3, 4, 4) End Sub Rick "Rick Rothstein (MVP - VB)" wrote in message ... If your BeforeDoubleClick event code answer the OP's question, then this slightly simplified code should also work... Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next Target.Interior.ColorIndex = Choose(Val(Target.Value), 3, 3, 4, 4) End Sub Rick "FSt1" wrote in message ... hi this should work Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) If Target.Value = "" Then Target.Interior.ColorIndex = xlnone Else If Target.Value = 1 Or Target.Value = 2 Then Target.Interior.ColorIndex = 3 Else If Target.Value = 3 Or Target.Value = 4 Then Target.Interior.ColorIndex = 4 End If End If End If End Sub however, you may find it easier to use conditional formating. highlight your range to color change. on the menu bar.... formatsconditional formating when the conditional formating box come up...2 conditions. if cell value is......less than or equal to......2 format to red click the add button if cell vaue is......greater than or equal to.....3 format to green. the cell will change color as you enter or change the data. no code needed. Regards FSt1 "DTLay" wrote: I am working on a script to change the background color of a cell when it is double-clicked. So far, I have this script, which works fine..... Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) If Target.Interior.ColorIndex = 50 Then Target.Interior.ColorIndex = xlNone Else Target.Interior.ColorIndex = 50 End If Cancel = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) End Sub I am trying to modify this so that the target color changes based on the cell value. For instance, this is a grading sheet, and I'd like all scores of 1 or 2 to change to red, while 3 and 4 change to green. I've tried using an IF THEN statement around the above code to look at the value of the cell but it isn't working, likely because of the coding. I wrote: IF Target.Value "2" Then...(original code) Else...(Original code with different color value) I am getting an error running this, and am hoping someone can tell me why. I haven't programmed script in a long time, so I'm hoping it's a simple fix. Thanks! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help modifying VB script
yeah, i've have days like that too.
regards FSt1 "Rick Rothstein (MVP - VB)" wrote: Of course, it would have been nice if I had used the same procedure header that you did... Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) On Error Resume Next Target.Interior.ColorIndex = Choose(Val(Target.Value), 3, 3, 4, 4) End Sub Rick "Rick Rothstein (MVP - VB)" wrote in message ... If your BeforeDoubleClick event code answer the OP's question, then this slightly simplified code should also work... Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next Target.Interior.ColorIndex = Choose(Val(Target.Value), 3, 3, 4, 4) End Sub Rick "FSt1" wrote in message ... hi this should work Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) If Target.Value = "" Then Target.Interior.ColorIndex = xlnone Else If Target.Value = 1 Or Target.Value = 2 Then Target.Interior.ColorIndex = 3 Else If Target.Value = 3 Or Target.Value = 4 Then Target.Interior.ColorIndex = 4 End If End If End If End Sub however, you may find it easier to use conditional formating. highlight your range to color change. on the menu bar.... formatsconditional formating when the conditional formating box come up...2 conditions. if cell value is......less than or equal to......2 format to red click the add button if cell vaue is......greater than or equal to.....3 format to green. the cell will change color as you enter or change the data. no code needed. Regards FSt1 "DTLay" wrote: I am working on a script to change the background color of a cell when it is double-clicked. So far, I have this script, which works fine..... Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) If Target.Interior.ColorIndex = 50 Then Target.Interior.ColorIndex = xlNone Else Target.Interior.ColorIndex = 50 End If Cancel = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) End Sub I am trying to modify this so that the target color changes based on the cell value. For instance, this is a grading sheet, and I'd like all scores of 1 or 2 to change to red, while 3 and 4 change to green. I've tried using an IF THEN statement around the above code to look at the value of the cell but it isn't working, likely because of the coding. I wrote: IF Target.Value "2" Then...(original code) Else...(Original code with different color value) I am getting an error running this, and am hoping someone can tell me why. I haven't programmed script in a long time, so I'm hoping it's a simple fix. Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Modifying If Statement | Excel Worksheet Functions | |||
| Modifying Macro | Excel Discussion (Misc queries) | |||
| help Modifying | Excel Worksheet Functions | |||
| Excel 2000/XP script to Excel97 script | Excel Programming | |||
| Help modifying code | Excel Programming | |||
Linear Mode
