You are here:

Excel/Macro to highlight a range if it contains ticket ranges

Advertisement


Question
Data's are pasted into cells in Range C which contains ticket ranges.

Below are the ticket ranges

ST388 - ST404
CR510 - CR528

What the macro should do -

As soon as the data is pasted in Range C, Range C should get highlighted in RED if the data contains above mentioned Ticket Ranges.

A dummy example of how the data looks -

ST388 xxx xxx xxxxx
CR510 xxx xxx xxxxx
ST389 xxx xxx xxxxx
CR512 xxx xxx xxxxx

Below code does this -

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim RngToCheck As Range
Set RngToCheck = Intersect(Sh.Columns(3), Target)
If Not RngToCheck Is Nothing Then
 For Each cll In RngToCheck.Cells
       cll.Interior.ColorIndex = xlNone
   Select Case UCase(Left(cll.Value, 2))
     Case "CR"
       myNo = Val(Mid(cll.Value, 3,3))
       If myNo >= 510 And myNo <= 528 Then cll.Interior.Color = RGB(255, 0, 0)
     Case "ST"
       myNo = Val(Mid(cll.Value, 3,3))
       If myNo >= 388 And myNo <= 404 Then cll.Interior.Color = RGB(255, 100, 0)
   End Select
 Next cll
End If
End Sub

However, I want the macro to also highlight Range C if there are spaces or no spaces before the Ticket Range. (Example as below)

  ST388 xxx xxx xxxxx (Spaces before ticket range)
ST389 xxx xxx xxxxx (No space before ticket range)
CR510 xxx xxx xxxxx (Space before ticket range)

Answer
Bimmy,

If you just want to strip out spaces you can use the trim command

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim s as String
Dim RngToCheck As Range
Set RngToCheck = Intersect(Sh.Columns(3), Target)
If Not RngToCheck Is Nothing Then
For Each cll In RngToCheck.Cells
      cll.Interior.ColorIndex = xlNone
      s = Ucase(Trim(cll.value))
  Select Case Left(s, 2)
    Case "CR"
      myNo = Val(Mid(s, 3,3))
      If myNo >= 510 And myNo <= 528 Then cll.Interior.Color = RGB(255, 0, 0)
    Case "ST"
      myNo = Val(Mid(s, 3,3))
      If myNo >= 388 And myNo <= 404 Then cll.Interior.Color = RGB(255, 100, 0)
  End Select
Next cll
End If
End Sub

I will caution that since you said pasted, if you are copying this from the web it is possible that what you call a space is not actually a space but a special character called a non-breaking space.  If the above code does not correct your problem, then additional code might need to be added to correct for a non-breaking space character.  You would have to let me know and I could try coding a correction for that.

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.