ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Need help modifying VB script (https://www.excelbanter.com/excel-programming/406719-need-help-modifying-vbulletin-script.html)

DTLay
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!

Rick Rothstein \(MVP - VB\)[_1348_]
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!


FSt1
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!

FSt1
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!

Rick Rothstein \(MVP - VB\)[_1349_]
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!


Rick Rothstein \(MVP - VB\)[_1350_]
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!

FSt1
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!


All times are GMT +1. The time now is 11:13 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com