Results 1 to 7 of 7

Thread: find case in a certain cell
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2017
    Posts
    468
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default find case in a certain cell

    hi,

    I use the following code
    Code:
      Dim Changed As Range, Cell As Range  
    Dim Clr As Long
       Set Changed = Intersect(Target, Range("m2:k300"))
      If Not Changed Is Nothing Then
        For Each Cell In Changed
           Select Case LCase(Cell.Value)
             Case "win10":       Clr = 35
             Case "no":        Clr = 55
             Case "issue":     Clr = 39
             Case "b i p":     Clr = 28
             Case "emailed":   Clr = 27
             Case "built":     Clr = 7
             Case "t b a":     Clr = 46
             Case "n / a":       Clr = 45
             Case "e u":       Clr = 22
             Case Else:        Clr = xlNone
          End Select
          Rows(Cell.Row).Resize(, Cell.Column).Interior.ColorIndex = Clr
        Next Cell
      End If
    It works fine, but is it vaible for 'Case "n / a"' to pick up a cell ref ( IE sheetname VB , cell ref a2 'Test' )


    Hoping this makes sense & thank you for your help.
    KR
    Trevor3007
    Thank you.
    Orbis non sufficit

    Trevor3007

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,086
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default Re: find case in a certain cell

    Quote Originally Posted by Trevor3007 View Post
    is it vaible for 'Case "n / a"' to pick up a cell ref ( IE sheetname VB , cell ref a2 'Test' )
    I don't understand what that means.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    Board Regular
    Join Date
    Jan 2017
    Posts
    468
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: find case in a certain cell

    Quote Originally Posted by AlphaFrog View Post
    I don't understand what that means.

    Hi,

    thanks for your reply & sorry for the mix up.


    curerently where it stats in the code ' Case "n / a" ', "n / a" may have to change , and rather than having to open the VB , it would look to a cell ( ie 'A2) and and would use the text in that cell.


    Hope this now makes sense

    KR
    Trevor3007
    Thank you.
    Orbis non sufficit

    Trevor3007

  4. #4
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,086
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default Re: find case in a certain cell

    Case LCase(Range("A2").Value): Clr = 45
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  5. #5
    Board Regular
    Join Date
    Jan 2017
    Posts
    468
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: find case in a certain cell

    Quote Originally Posted by AlphaFrog View Post
    Case LCase(Range("A2").Value): Clr = 45

    many thanks , very much appreciated.

    however, the 'text' is in sheet4(Vb) cell a2. can you help ?

    KR
    trevor3007
    Thank you.
    Orbis non sufficit

    Trevor3007

  6. #6
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,086
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default Re: find case in a certain cell

    Case LCase(Sheet4.Range("A2").Value): Clr = 45
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  7. #7
    Board Regular
    Join Date
    Jan 2017
    Posts
    468
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: find case in a certain cell

    Quote Originally Posted by AlphaFrog View Post
    Case LCase(Sheet4.Range("A2").Value): Clr = 45

    FANTASTIC!!!1

    Youre a VB wizard!!! Have agreat day.

    Thank you.
    Orbis non sufficit

    Trevor3007

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •