Hi Keith,
You can force the upper-case by using the 'UCase' VBA function on the 'Target'
You can then remove the lower-case Cases.
Private Sub Worksheet_Change(ByVal Target As Range)
Set I = Intersect(Target,
Range("I9:AM58,I66:AM115,I124:AM173,I182:AM231,I240:AM289"))
If Not I Is Nothing Then
Select Case UCase(Target)
Case "V": NewColor = 3
Case "E": NewColor = 33
Case "M": NewColor = 36
Case "S": NewColor = 4
End Select
Target.Interior.ColorIndex = NewColor
End If
End Sub
A simple way to allow mass deletions is to insert an error handling line:
On Error Resume Next
This effectively ignores errors, which seems to work for us in this case.
Try this with mass deletions:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Set I = Intersect(Target,
Range("I9:AM58,I66:AM115,I124:AM173,I182:AM231,I240:AM289"))
If Not I Is Nothing Then
Select Case UCase(Target)
Case "V": NewColor = 3
Case "E": NewColor = 33
Case "M": NewColor = 36
Case "S": NewColor = 4
End Select
Target.Interior.ColorIndex = NewColor
End If
End Sub
Hope this helps.
Regards - Dave.
> Date: Sun, 14 Feb 2010 18:41:40 -0800
> Subject: $$Excel-Macros$$ Conditional formatting Excel 2002
> From: dkb...@gmail.com
> To: excel-macros@googlegroups.com
>
> I consider myself a self-taught novice user of VBA for Excel. I took
> on a project at work and developed this VBA code with the help of the
> internet to format the background color of this range. Is there a way
> to force the uppercase version of the letters v, e, s and m if typed
> in lower case and still keep the background color for that cell? And
> a final question, is there a way to incorporate a command button to
> delete only the text and background color and still leave the border
> untouched in each cell within this range at the beginning of a new
> month? I can delete but I have to delete one cell at a time; if I
> select several cells at a time and push delete I get an error message
> highlighting "Case "v"" in yellow in the VB editor.
>
> Any help is greatly appreciated.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Set I = Intersect(Target,
> Range("I9:AM58,I66:AM115,I124:AM173,I182:AM231,I240:AM289"))
> If Not I Is Nothing Then
> Select Case Target
> Case "v": NewColor = 3
> Case "V": NewColor = 3
> Case "e": NewColor = 33
> Case "E": NewColor = 33
> Case "m": NewColor = 36
> Case "M": NewColor = 36
> Case "s": NewColor = 4
> Case "S": NewColor = 4
> End Select
> Target.Interior.ColorIndex = NewColor
> End If
>
> End Sub
>
> Keith
> dkb...@gmail.com
>
> --
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our Facebook Group @
> http://www.facebook.com/group.php?gid=287779555678
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
>
> We reach over 6,800 subscribers worldwide and receive many nice notes about
> the learning and support from the group.Let friends and co-workers know they
> can subscribe to group at
> http://groups.google.com/group/excel-macros/subscribe
_________________________________________________________________
Link all your email accounts and social updates with Hotmail. Find out now
http://windowslive.ninemsn.com.au/oneinbox?ocid=T162MSN05A0710G
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
