Cell and font color

[rmayer]

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 Phillips]

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]

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]

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]

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]

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]

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.

 

[rmayer]

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

 

[Bob Phillips]

Can't see many other options so go ahead.

--

HTH

Bob Phillips

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