I use code like the short example below as worksheet event code to
change cell and font color in various work sheets. It works perfect
with numeric cell values but now I need to modify it to work with
"cel.value = A" (there will be A thru F) instead of 1.
This will apply to a different range of cells than the numeric, as
maybe B1:B10.
I assume the term "IsNumeric(cel.Value)" needs to be changed, but I
can't figure out what it should be.
Using Office 2000
Can anyone please help?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For Each cel In Range("A1:A10").Cells
If IsNumeric(cel.Value) And cel.Value <> "" Then
If cel.Value = 1 Then
cel.Font.ColorIndex = 1
cel.Interior.ColorIndex = 4
ElseIf cel.Value = 2 Then
cel.Font.ColorIndex = 1
cel.Interior.ColorIndex = 45
End If
Else
cel.Font.ColorIndex = 1
cel.Interior.ColorIndex = 0
End If
Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("B1:B10")) Is Nothing Then
With Target
Select Case .Value
Case "A":
.Font.ColorIndex = 1
.Interior.ColorIndex = 4
Case "B":
.Font.ColorIndex = 1
.Interior.ColorIndex = 45
'etc
Case Else
.Font.ColorIndex = 1
.Interior.ColorIndex = 0
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"rmayer" <(E-Mail Removed)> wrote in message
news:8CF531B0-046F-455A-8DC2-(E-Mail Removed)...
> I use code like the short example below as worksheet event code to
> change cell and font color in various work sheets. It works perfect
> with numeric cell values but now I need to modify it to work with
> "cel.value = A" (there will be A thru F) instead of 1.
> This will apply to a different range of cells than the numeric, as
> maybe B1:B10.
> I assume the term "IsNumeric(cel.Value)" needs to be changed, but I
> can't figure out what it should be.
> Using Office 2000
> Can anyone please help?
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> For Each cel In Range("A1:A10").Cells
> If IsNumeric(cel.Value) And cel.Value <> "" Then
> If cel.Value = 1 Then
> cel.Font.ColorIndex = 1
> cel.Interior.ColorIndex = 4
> ElseIf cel.Value = 2 Then
> cel.Font.ColorIndex = 1
> cel.Interior.ColorIndex = 45
> End If
> Else
> cel.Font.ColorIndex = 1
> cel.Interior.ColorIndex = 0
> End If
> Next
> End Sub

Bob,
Thanks for the reply, the code works but not as I need.
I need it to work as part of the code I already use.
A1:A10 as numeric B1:B10 as A,B etc. Values in
A1:A10 are from data pasted from another source into
D110 and values in B1:B10 are from E1:E10.
This code apparently requires direct entry in each cell.
With several cell ranges in several workbooks this will be
too time consuming.
Thats why I was looking for a replacement term for "IsNumeric"
that works for A,B instead of numeric values.
Conditional formatting has only 3 colors. I need 4 or more.

"Bob Phillips" wrote:

> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Application.EnableEvents = False
> On Error GoTo ws_exit
> If Not Intersect(Target, Range("B1:B10")) Is Nothing Then
> With Target
> Select Case .Value
> Case "A":
> .Font.ColorIndex = 1
> .Interior.ColorIndex = 4
> Case "B":
> .Font.ColorIndex = 1
> .Interior.ColorIndex = 45
> 'etc
> Case Else
> .Font.ColorIndex = 1
> .Interior.ColorIndex = 0
> End Select
> End With
> End If
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> "rmayer" <(E-Mail Removed)> wrote in message
> news:8CF531B0-046F-455A-8DC2-(E-Mail Removed)...
> > I use code like the short example below as worksheet event code to
> > change cell and font color in various work sheets. It works perfect
> > with numeric cell values but now I need to modify it to work with
> > "cel.value = A" (there will be A thru F) instead of 1.
> > This will apply to a different range of cells than the numeric, as
> > maybe B1:B10.
> > I assume the term "IsNumeric(cel.Value)" needs to be changed, but I
> > can't figure out what it should be.
> > Using Office 2000
> > Can anyone please help?
> >
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > For Each cel In Range("A1:A10").Cells
> > If IsNumeric(cel.Value) And cel.Value <> "" Then
> > If cel.Value = 1 Then
> > cel.Font.ColorIndex = 1
> > cel.Interior.ColorIndex = 4
> > ElseIf cel.Value = 2 Then
> > cel.Font.ColorIndex = 1
> > cel.Interior.ColorIndex = 45
> > End If
> > Else
> > cel.Font.ColorIndex = 1
> > cel.Interior.ColorIndex = 0
> > End If
> > Next
> > End Sub
>
>
>

This changes the colour as the value is changed, yours changes many cells as
you select any cell, hugely inefficient.

The many workbooks is just as much an issue for your approach as for mine,
you will need to add the code to all.

You can also re-use the code you have in conjunction with this, but I showed
you the Select Case form as I think it is much tidier, more readable, more
maintainable.

As to IsNumeric, it is not necessary if you test for each value, but you
could always just add a If Not IsNumeric(Target.Value) test to the code.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"rmayer" <(E-Mail Removed)> wrote in message
news:5385304D-FCFD-49F8-BD15-(E-Mail Removed)...
> Bob,
> Thanks for the reply, the code works but not as I need.
> I need it to work as part of the code I already use.
> A1:A10 as numeric B1:B10 as A,B etc. Values in
> A1:A10 are from data pasted from another source into
> D110 and values in B1:B10 are from E1:E10.
> This code apparently requires direct entry in each cell.
> With several cell ranges in several workbooks this will be
> too time consuming.
> Thats why I was looking for a replacement term for "IsNumeric"
> that works for A,B instead of numeric values.
> Conditional formatting has only 3 colors. I need 4 or more.
>
> "Bob Phillips" wrote:
>
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >
> > Application.EnableEvents = False
> > On Error GoTo ws_exit
> > If Not Intersect(Target, Range("B1:B10")) Is Nothing Then
> > With Target
> > Select Case .Value
> > Case "A":
> > .Font.ColorIndex = 1
> > .Interior.ColorIndex = 4
> > Case "B":
> > .Font.ColorIndex = 1
> > .Interior.ColorIndex = 45
> > 'etc
> > Case Else
> > .Font.ColorIndex = 1
> > .Interior.ColorIndex = 0
> > End Select
> > End With
> > End If
> >
> > ws_exit:
> > Application.EnableEvents = True
> > End Sub
> >
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (replace xxxx in the email address with gmail if mailing direct)
> >
> > "rmayer" <(E-Mail Removed)> wrote in message
> > news:8CF531B0-046F-455A-8DC2-(E-Mail Removed)...
> > > I use code like the short example below as worksheet event code to
> > > change cell and font color in various work sheets. It works perfect
> > > with numeric cell values but now I need to modify it to work with
> > > "cel.value = A" (there will be A thru F) instead of 1.
> > > This will apply to a different range of cells than the numeric, as
> > > maybe B1:B10.
> > > I assume the term "IsNumeric(cel.Value)" needs to be changed, but I
> > > can't figure out what it should be.
> > > Using Office 2000
> > > Can anyone please help?
> > >
> > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > For Each cel In Range("A1:A10").Cells
> > > If IsNumeric(cel.Value) And cel.Value <> "" Then
> > > If cel.Value = 1 Then
> > > cel.Font.ColorIndex = 1
> > > cel.Interior.ColorIndex = 4
> > > ElseIf cel.Value = 2 Then
> > > cel.Font.ColorIndex = 1
> > > cel.Interior.ColorIndex = 45
> > > End If
> > > Else
> > > cel.Font.ColorIndex = 1
> > > cel.Interior.ColorIndex = 0
> > > End If
> > > Next
> > > End Sub
> >
> >
> >

Bob,
I know very little about writing this code. It may be inefficient as you
say, but
the numeric section similar to what I posted works. I have it running in
several
workbooks. I tried "If Not IsNumeric(Target.Value)". No error messages,
just didn't work.
I noticed when I added your code example to a test workbook, the
paste function completely quit working ? Is that expected behavior?
Many thanks for your help.

"Bob Phillips" wrote:

> This changes the colour as the value is changed, yours changes many cells as
> you select any cell, hugely inefficient.
>
> The many workbooks is just as much an issue for your approach as for mine,
> you will need to add the code to all.
>
> You can also re-use the code you have in conjunction with this, but I showed
> you the Select Case form as I think it is much tidier, more readable, more
> maintainable.
>
> As to IsNumeric, it is not necessary if you test for each value, but you
> could always just add a If Not IsNumeric(Target.Value) test to the code.
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> "rmayer" <(E-Mail Removed)> wrote in message
> news:5385304D-FCFD-49F8-BD15-(E-Mail Removed)...
> > Bob,
> > Thanks for the reply, the code works but not as I need.
> > I need it to work as part of the code I already use.
> > A1:A10 as numeric B1:B10 as A,B etc. Values in
> > A1:A10 are from data pasted from another source into
> > D110 and values in B1:B10 are from E1:E10.
> > This code apparently requires direct entry in each cell.
> > With several cell ranges in several workbooks this will be
> > too time consuming.
> > Thats why I was looking for a replacement term for "IsNumeric"
> > that works for A,B instead of numeric values.
> > Conditional formatting has only 3 colors. I need 4 or more.
> >
> > "Bob Phillips" wrote:
> >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > >
> > > Application.EnableEvents = False
> > > On Error GoTo ws_exit
> > > If Not Intersect(Target, Range("B1:B10")) Is Nothing Then
> > > With Target
> > > Select Case .Value
> > > Case "A":
> > > .Font.ColorIndex = 1
> > > .Interior.ColorIndex = 4
> > > Case "B":
> > > .Font.ColorIndex = 1
> > > .Interior.ColorIndex = 45
> > > 'etc
> > > Case Else
> > > .Font.ColorIndex = 1
> > > .Interior.ColorIndex = 0
> > > End Select
> > > End With
> > > End If
> > >
> > > ws_exit:
> > > Application.EnableEvents = True
> > > End Sub
> > >
> > >
> > > --
> > >
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace xxxx in the email address with gmail if mailing direct)
> > >
> > > "rmayer" <(E-Mail Removed)> wrote in message
> > > news:8CF531B0-046F-455A-8DC2-(E-Mail Removed)...
> > > > I use code like the short example below as worksheet event code to
> > > > change cell and font color in various work sheets. It works perfect
> > > > with numeric cell values but now I need to modify it to work with
> > > > "cel.value = A" (there will be A thru F) instead of 1.
> > > > This will apply to a different range of cells than the numeric, as
> > > > maybe B1:B10.
> > > > I assume the term "IsNumeric(cel.Value)" needs to be changed, but I
> > > > can't figure out what it should be.
> > > > Using Office 2000
> > > > Can anyone please help?
> > > >
> > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > For Each cel In Range("A1:A10").Cells
> > > > If IsNumeric(cel.Value) And cel.Value <> "" Then
> > > > If cel.Value = 1 Then
> > > > cel.Font.ColorIndex = 1
> > > > cel.Interior.ColorIndex = 4
> > > > ElseIf cel.Value = 2 Then
> > > > cel.Font.ColorIndex = 1
> > > > cel.Interior.ColorIndex = 45
> > > > End If
> > > > Else
> > > > cel.Font.ColorIndex = 1
> > > > cel.Interior.ColorIndex = 0
> > > > End If
> > > > Next
> > > > End Sub
> > >
> > >
> > >
>
>
>

The problem would appear to be that you don't understand what I am
suggesting, and I can't see what you have done, so we are nor meeting in the
middle.

And what paste function are you referring to, your code only sets a colour
for numeric values?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"rmayer" <(E-Mail Removed)> wrote in message
news:0EF78F10-33D1-4F56-9341-(E-Mail Removed)...
> Bob,
> I know very little about writing this code. It may be inefficient as you
> say, but
> the numeric section similar to what I posted works. I have it running in
> several
> workbooks. I tried "If Not IsNumeric(Target.Value)". No error messages,
> just didn't work.
> I noticed when I added your code example to a test workbook, the
> paste function completely quit working ? Is that expected behavior?
> Many thanks for your help.
>
> "Bob Phillips" wrote:
>
> > This changes the colour as the value is changed, yours changes many
cells as
> > you select any cell, hugely inefficient.
> >
> > The many workbooks is just as much an issue for your approach as for
mine,
> > you will need to add the code to all.
> >
> > You can also re-use the code you have in conjunction with this, but I
showed
> > you the Select Case form as I think it is much tidier, more readable,
more
> > maintainable.
> >
> > As to IsNumeric, it is not necessary if you test for each value, but you
> > could always just add a If Not IsNumeric(Target.Value) test to the code.
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (replace xxxx in the email address with gmail if mailing direct)
> >
> > "rmayer" <(E-Mail Removed)> wrote in message
> > news:5385304D-FCFD-49F8-BD15-(E-Mail Removed)...
> > > Bob,
> > > Thanks for the reply, the code works but not as I need.
> > > I need it to work as part of the code I already use.
> > > A1:A10 as numeric B1:B10 as A,B etc. Values in
> > > A1:A10 are from data pasted from another source into
> > > D110 and values in B1:B10 are from E1:E10.
> > > This code apparently requires direct entry in each cell.
> > > With several cell ranges in several workbooks this will be
> > > too time consuming.
> > > Thats why I was looking for a replacement term for "IsNumeric"
> > > that works for A,B instead of numeric values.
> > > Conditional formatting has only 3 colors. I need 4 or more.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > >
> > > > Application.EnableEvents = False
> > > > On Error GoTo ws_exit
> > > > If Not Intersect(Target, Range("B1:B10")) Is Nothing Then
> > > > With Target
> > > > Select Case .Value
> > > > Case "A":
> > > > .Font.ColorIndex = 1
> > > > .Interior.ColorIndex = 4
> > > > Case "B":
> > > > .Font.ColorIndex = 1
> > > > .Interior.ColorIndex = 45
> > > > 'etc
> > > > Case Else
> > > > .Font.ColorIndex = 1
> > > > .Interior.ColorIndex = 0
> > > > End Select
> > > > End With
> > > > End If
> > > >
> > > > ws_exit:
> > > > Application.EnableEvents = True
> > > > End Sub
> > > >
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (replace xxxx in the email address with gmail if mailing direct)
> > > >
> > > > "rmayer" <(E-Mail Removed)> wrote in message
> > > > news:8CF531B0-046F-455A-8DC2-(E-Mail Removed)...
> > > > > I use code like the short example below as worksheet event code to
> > > > > change cell and font color in various work sheets. It works
perfect
> > > > > with numeric cell values but now I need to modify it to work with
> > > > > "cel.value = A" (there will be A thru F) instead of 1.
> > > > > This will apply to a different range of cells than the numeric, as
> > > > > maybe B1:B10.
> > > > > I assume the term "IsNumeric(cel.Value)" needs to be changed, but
I
> > > > > can't figure out what it should be.
> > > > > Using Office 2000
> > > > > Can anyone please help?
> > > > >
> > > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > > For Each cel In Range("A1:A10").Cells
> > > > > If IsNumeric(cel.Value) And cel.Value <> "" Then
> > > > > If cel.Value = 1 Then
> > > > > cel.Font.ColorIndex = 1
> > > > > cel.Interior.ColorIndex = 4
> > > > > ElseIf cel.Value = 2 Then
> > > > > cel.Font.ColorIndex = 1
> > > > > cel.Interior.ColorIndex = 45
> > > > > End If
> > > > > Else
> > > > > cel.Font.ColorIndex = 1
> > > > > cel.Interior.ColorIndex = 0
> > > > > End If
> > > > > Next
> > > > > End Sub
> > > >
> > > >
> > > >
> >
> >
> >

As I stated, the values in A1:A10 come from D110 (as =D1). Values in
D110 are pasted in from another data source and some math is done in the
process.
Values in B1:B10 come from E1:E10 and are generated by formula from data
pated in D110. This a grade rating system. A1 could be 95%, green cell, B1
then would show A in a green cell, etc.
The sample code you posted disables the paste function in my workbook
completely.
The numeric code I've been using does not. Possible I'm not applying it
correctly, but, I don't see how.

"Bob Phillips" wrote:

> The problem would appear to be that you don't understand what I am
> suggesting, and I can't see what you have done, so we are nor meeting in the
> middle.
>
> And what paste function are you referring to, your code only sets a colour
> for numeric values?
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> "rmayer" <(E-Mail Removed)> wrote in message
> news:0EF78F10-33D1-4F56-9341-(E-Mail Removed)...
> > Bob,
> > I know very little about writing this code. It may be inefficient as you
> > say, but
> > the numeric section similar to what I posted works. I have it running in
> > several
> > workbooks. I tried "If Not IsNumeric(Target.Value)". No error messages,
> > just didn't work.
> > I noticed when I added your code example to a test workbook, the
> > paste function completely quit working ? Is that expected behavior?
> > Many thanks for your help.
> >
> > "Bob Phillips" wrote:
> >
> > > This changes the colour as the value is changed, yours changes many
> cells as
> > > you select any cell, hugely inefficient.
> > >
> > > The many workbooks is just as much an issue for your approach as for
> mine,
> > > you will need to add the code to all.
> > >
> > > You can also re-use the code you have in conjunction with this, but I
> showed
> > > you the Select Case form as I think it is much tidier, more readable,
> more
> > > maintainable.
> > >
> > > As to IsNumeric, it is not necessary if you test for each value, but you
> > > could always just add a If Not IsNumeric(Target.Value) test to the code.
> > >
> > > --
> > >
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace xxxx in the email address with gmail if mailing direct)
> > >
> > > "rmayer" <(E-Mail Removed)> wrote in message
> > > news:5385304D-FCFD-49F8-BD15-(E-Mail Removed)...
> > > > Bob,
> > > > Thanks for the reply, the code works but not as I need.
> > > > I need it to work as part of the code I already use.
> > > > A1:A10 as numeric B1:B10 as A,B etc. Values in
> > > > A1:A10 are from data pasted from another source into
> > > > D110 and values in B1:B10 are from E1:E10.
> > > > This code apparently requires direct entry in each cell.
> > > > With several cell ranges in several workbooks this will be
> > > > too time consuming.
> > > > Thats why I was looking for a replacement term for "IsNumeric"
> > > > that works for A,B instead of numeric values.
> > > > Conditional formatting has only 3 colors. I need 4 or more.
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > >
> > > > > Application.EnableEvents = False
> > > > > On Error GoTo ws_exit
> > > > > If Not Intersect(Target, Range("B1:B10")) Is Nothing Then
> > > > > With Target
> > > > > Select Case .Value
> > > > > Case "A":
> > > > > .Font.ColorIndex = 1
> > > > > .Interior.ColorIndex = 4
> > > > > Case "B":
> > > > > .Font.ColorIndex = 1
> > > > > .Interior.ColorIndex = 45
> > > > > 'etc
> > > > > Case Else
> > > > > .Font.ColorIndex = 1
> > > > > .Interior.ColorIndex = 0
> > > > > End Select
> > > > > End With
> > > > > End If
> > > > >
> > > > > ws_exit:
> > > > > Application.EnableEvents = True
> > > > > End Sub
> > > > >
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > (replace xxxx in the email address with gmail if mailing direct)
> > > > >
> > > > > "rmayer" <(E-Mail Removed)> wrote in message
> > > > > news:8CF531B0-046F-455A-8DC2-(E-Mail Removed)...
> > > > > > I use code like the short example below as worksheet event code to
> > > > > > change cell and font color in various work sheets. It works
> perfect
> > > > > > with numeric cell values but now I need to modify it to work with
> > > > > > "cel.value = A" (there will be A thru F) instead of 1.
> > > > > > This will apply to a different range of cells than the numeric, as
> > > > > > maybe B1:B10.
> > > > > > I assume the term "IsNumeric(cel.Value)" needs to be changed, but
> I
> > > > > > can't figure out what it should be.
> > > > > > Using Office 2000
> > > > > > Can anyone please help?
> > > > > >
> > > > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > > > For Each cel In Range("A1:A10").Cells
> > > > > > If IsNumeric(cel.Value) And cel.Value <> "" Then
> > > > > > If cel.Value = 1 Then
> > > > > > cel.Font.ColorIndex = 1
> > > > > > cel.Interior.ColorIndex = 4
> > > > > > ElseIf cel.Value = 2 Then
> > > > > > cel.Font.ColorIndex = 1
> > > > > > cel.Interior.ColorIndex = 45
> > > > > > End If
> > > > > > Else
> > > > > > cel.Font.ColorIndex = 1
> > > > > > cel.Interior.ColorIndex = 0
> > > > > > End If
> > > > > > Next
> > > > > > End Sub
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Bob,
I could e-mail you the work sheet with details on how its used ?
Thanks again

"rmayer" wrote:

> As I stated, the values in A1:A10 come from D110 (as =D1). Values in
> D110 are pasted in from another data source and some math is done in the
> process.
> Values in B1:B10 come from E1:E10 and are generated by formula from data
> pated in D110. This a grade rating system. A1 could be 95%, green cell, B1
> then would show A in a green cell, etc.
> The sample code you posted disables the paste function in my workbook
> completely.
> The numeric code I've been using does not. Possible I'm not applying it
> correctly, but, I don't see how.
>
> "Bob Phillips" wrote:
>
> > The problem would appear to be that you don't understand what I am
> > suggesting, and I can't see what you have done, so we are nor meeting in the
> > middle.
> >
> > And what paste function are you referring to, your code only sets a colour
> > for numeric values?
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (replace xxxx in the email address with gmail if mailing direct)
> >
> > "rmayer" <(E-Mail Removed)> wrote in message
> > news:0EF78F10-33D1-4F56-9341-(E-Mail Removed)...
> > > Bob,
> > > I know very little about writing this code. It may be inefficient as you
> > > say, but
> > > the numeric section similar to what I posted works. I have it running in
> > > several
> > > workbooks. I tried "If Not IsNumeric(Target.Value)". No error messages,
> > > just didn't work.
> > > I noticed when I added your code example to a test workbook, the
> > > paste function completely quit working ? Is that expected behavior?
> > > Many thanks for your help.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > This changes the colour as the value is changed, yours changes many
> > cells as
> > > > you select any cell, hugely inefficient.
> > > >
> > > > The many workbooks is just as much an issue for your approach as for
> > mine,
> > > > you will need to add the code to all.
> > > >
> > > > You can also re-use the code you have in conjunction with this, but I
> > showed
> > > > you the Select Case form as I think it is much tidier, more readable,
> > more
> > > > maintainable.
> > > >
> > > > As to IsNumeric, it is not necessary if you test for each value, but you
> > > > could always just add a If Not IsNumeric(Target.Value) test to the code.
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (replace xxxx in the email address with gmail if mailing direct)
> > > >
> > > > "rmayer" <(E-Mail Removed)> wrote in message
> > > > news:5385304D-FCFD-49F8-BD15-(E-Mail Removed)...
> > > > > Bob,
> > > > > Thanks for the reply, the code works but not as I need.
> > > > > I need it to work as part of the code I already use.
> > > > > A1:A10 as numeric B1:B10 as A,B etc. Values in
> > > > > A1:A10 are from data pasted from another source into
> > > > > D110 and values in B1:B10 are from E1:E10.
> > > > > This code apparently requires direct entry in each cell.
> > > > > With several cell ranges in several workbooks this will be
> > > > > too time consuming.
> > > > > Thats why I was looking for a replacement term for "IsNumeric"
> > > > > that works for A,B instead of numeric values.
> > > > > Conditional formatting has only 3 colors. I need 4 or more.
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > > >
> > > > > > Application.EnableEvents = False
> > > > > > On Error GoTo ws_exit
> > > > > > If Not Intersect(Target, Range("B1:B10")) Is Nothing Then
> > > > > > With Target
> > > > > > Select Case .Value
> > > > > > Case "A":
> > > > > > .Font.ColorIndex = 1
> > > > > > .Interior.ColorIndex = 4
> > > > > > Case "B":
> > > > > > .Font.ColorIndex = 1
> > > > > > .Interior.ColorIndex = 45
> > > > > > 'etc
> > > > > > Case Else
> > > > > > .Font.ColorIndex = 1
> > > > > > .Interior.ColorIndex = 0
> > > > > > End Select
> > > > > > End With
> > > > > > End If
> > > > > >
> > > > > > ws_exit:
> > > > > > Application.EnableEvents = True
> > > > > > End Sub
> > > > > >
> > > > > >
> > > > > > --
> > > > > >
> > > > > > HTH
> > > > > >
> > > > > > Bob Phillips
> > > > > >
> > > > > > (replace xxxx in the email address with gmail if mailing direct)
> > > > > >
> > > > > > "rmayer" <(E-Mail Removed)> wrote in message
> > > > > > news:8CF531B0-046F-455A-8DC2-(E-Mail Removed)...
> > > > > > > I use code like the short example below as worksheet event code to
> > > > > > > change cell and font color in various work sheets. It works
> > perfect
> > > > > > > with numeric cell values but now I need to modify it to work with
> > > > > > > "cel.value = A" (there will be A thru F) instead of 1.
> > > > > > > This will apply to a different range of cells than the numeric, as
> > > > > > > maybe B1:B10.
> > > > > > > I assume the term "IsNumeric(cel.Value)" needs to be changed, but
> > I
> > > > > > > can't figure out what it should be.
> > > > > > > Using Office 2000
> > > > > > > Can anyone please help?
> > > > > > >
> > > > > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > > > > For Each cel In Range("A1:A10").Cells
> > > > > > > If IsNumeric(cel.Value) And cel.Value <> "" Then
> > > > > > > If cel.Value = 1 Then
> > > > > > > cel.Font.ColorIndex = 1
> > > > > > > cel.Interior.ColorIndex = 4
> > > > > > > ElseIf cel.Value = 2 Then
> > > > > > > cel.Font.ColorIndex = 1
> > > > > > > cel.Interior.ColorIndex = 45
> > > > > > > End If
> > > > > > > Else
> > > > > > > cel.Font.ColorIndex = 1
> > > > > > > cel.Interior.ColorIndex = 0
> > > > > > > End If
> > > > > > > Next
> > > > > > > End Sub
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Can't see many other options so go ahead.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"rmayer" <(E-Mail Removed)> wrote in message
news:04B84BEC-A543-45DC-BD7C-(E-Mail Removed)...
> Bob,
> I could e-mail you the work sheet with details on how its used ?
> Thanks again
>
> "rmayer" wrote:
>
> > As I stated, the values in A1:A10 come from D110 (as =D1). Values in
> > D110 are pasted in from another data source and some math is done in
the
> > process.
> > Values in B1:B10 come from E1:E10 and are generated by formula from data
> > pated in D110. This a grade rating system. A1 could be 95%, green
cell, B1
> > then would show A in a green cell, etc.
> > The sample code you posted disables the paste function in my workbook
> > completely.
> > The numeric code I've been using does not. Possible I'm not applying it
> > correctly, but, I don't see how.
> >
> > "Bob Phillips" wrote:
> >
> > > The problem would appear to be that you don't understand what I am
> > > suggesting, and I can't see what you have done, so we are nor meeting
in the
> > > middle.
> > >
> > > And what paste function are you referring to, your code only sets a
colour
> > > for numeric values?
> > >
> > > --
> > >
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace xxxx in the email address with gmail if mailing direct)
> > >
> > > "rmayer" <(E-Mail Removed)> wrote in message
> > > news:0EF78F10-33D1-4F56-9341-(E-Mail Removed)...
> > > > Bob,
> > > > I know very little about writing this code. It may be inefficient as
you
> > > > say, but
> > > > the numeric section similar to what I posted works. I have it
running in
> > > > several
> > > > workbooks. I tried "If Not IsNumeric(Target.Value)". No error
messages,
> > > > just didn't work.
> > > > I noticed when I added your code example to a test workbook, the
> > > > paste function completely quit working ? Is that expected behavior?
> > > > Many thanks for your help.
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > This changes the colour as the value is changed, yours changes
many
> > > cells as
> > > > > you select any cell, hugely inefficient.
> > > > >
> > > > > The many workbooks is just as much an issue for your approach as
for
> > > mine,
> > > > > you will need to add the code to all.
> > > > >
> > > > > You can also re-use the code you have in conjunction with this,
but I
> > > showed
> > > > > you the Select Case form as I think it is much tidier, more
readable,
> > > more
> > > > > maintainable.
> > > > >
> > > > > As to IsNumeric, it is not necessary if you test for each value,
but you
> > > > > could always just add a If Not IsNumeric(Target.Value) test to the
code.
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > (replace xxxx in the email address with gmail if mailing direct)
> > > > >
> > > > > "rmayer" <(E-Mail Removed)> wrote in message
> > > > > news:5385304D-FCFD-49F8-BD15-(E-Mail Removed)...
> > > > > > Bob,
> > > > > > Thanks for the reply, the code works but not as I need.
> > > > > > I need it to work as part of the code I already use.
> > > > > > A1:A10 as numeric B1:B10 as A,B etc. Values in
> > > > > > A1:A10 are from data pasted from another source into
> > > > > > D110 and values in B1:B10 are from E1:E10.
> > > > > > This code apparently requires direct entry in each cell.
> > > > > > With several cell ranges in several workbooks this will be
> > > > > > too time consuming.
> > > > > > Thats why I was looking for a replacement term for "IsNumeric"
> > > > > > that works for A,B instead of numeric values.
> > > > > > Conditional formatting has only 3 colors. I need 4 or more.
> > > > > >
> > > > > > "Bob Phillips" wrote:
> > > > > >
> > > > > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > > > >
> > > > > > > Application.EnableEvents = False
> > > > > > > On Error GoTo ws_exit
> > > > > > > If Not Intersect(Target, Range("B1:B10")) Is Nothing Then
> > > > > > > With Target
> > > > > > > Select Case .Value
> > > > > > > Case "A":
> > > > > > > .Font.ColorIndex = 1
> > > > > > > .Interior.ColorIndex = 4
> > > > > > > Case "B":
> > > > > > > .Font.ColorIndex = 1
> > > > > > > .Interior.ColorIndex = 45
> > > > > > > 'etc
> > > > > > > Case Else
> > > > > > > .Font.ColorIndex = 1
> > > > > > > .Interior.ColorIndex = 0
> > > > > > > End Select
> > > > > > > End With
> > > > > > > End If
> > > > > > >
> > > > > > > ws_exit:
> > > > > > > Application.EnableEvents = True
> > > > > > > End Sub
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > HTH
> > > > > > >
> > > > > > > Bob Phillips
> > > > > > >
> > > > > > > (replace xxxx in the email address with gmail if mailing
direct)
> > > > > > >
> > > > > > > "rmayer" <(E-Mail Removed)> wrote in message
> > > > > > > news:8CF531B0-046F-455A-8DC2-(E-Mail Removed)...
> > > > > > > > I use code like the short example below as worksheet event
code to
> > > > > > > > change cell and font color in various work sheets. It works
> > > perfect
> > > > > > > > with numeric cell values but now I need to modify it to work
with
> > > > > > > > "cel.value = A" (there will be A thru F) instead of 1.
> > > > > > > > This will apply to a different range of cells than the
numeric, as
> > > > > > > > maybe B1:B10.
> > > > > > > > I assume the term "IsNumeric(cel.Value)" needs to be
changed, but
> > > I
> > > > > > > > can't figure out what it should be.
> > > > > > > > Using Office 2000
> > > > > > > > Can anyone please help?
> > > > > > > >
> > > > > > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > > > > > For Each cel In Range("A1:A10").Cells
> > > > > > > > If IsNumeric(cel.Value) And cel.Value <> "" Then
> > > > > > > > If cel.Value = 1 Then
> > > > > > > > cel.Font.ColorIndex = 1
> > > > > > > > cel.Interior.ColorIndex = 4
> > > > > > > > ElseIf cel.Value = 2 Then
> > > > > > > > cel.Font.ColorIndex = 1
> > > > > > > > cel.Interior.ColorIndex = 45
> > > > > > > > End If
> > > > > > > > Else
> > > > > > > > cel.Font.ColorIndex = 1
> > > > > > > > cel.Interior.ColorIndex = 0
> > > > > > > > End If
> > > > > > > > Next
> > > > > > > > End Sub
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >