Hi Keith, Would you please send the Excel file where the changes are required ?
Best regards, Ayush Jain On Feb 15, 7:41 am, Keith <dkb...@gmail.com> wrote: > 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