Results 1 to 9 of 9

Thread: Highlight cell based on values

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Highlight cell based on values

    Hello,

    i have multiple tabs in file and have to Highlight column from N to AL if values are matching in row AU to BF

    below is link i have saved file on google drive.

    https://drive.google.com/file/d/1FYh4lIrxGrvTeLdB7bP52DI4xinyhZv9/view


    tnx alot for your precious time and valuable feedback across post.

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,249
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Highlight cell based on values

    Try this macro. I think that the value 34115 should be 3415.
    Code:
    Sub MatchVals()
        Application.ScreenUpdating = False
        Dim LastRow As Long, ws As Worksheet, rng As Range, x As Long, y As Long, val As String, fnd As Range
        For Each ws In Sheets
            LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            For x = 14 To 38
                If x <> 26 Then
                    For y = 2 To LastRow
                        For Each rng In ws.Range(ws.Cells(y, x), ws.Cells(y + 3, x))
                            val = val & rng
                        Next rng
                        Set fnd = ws.Range("AU1:BF1").Find(val, LookIn:=xlValues, lookat:=xlWhole)
                        If Not fnd Is Nothing Then
                            ws.Range(ws.Cells(y, x), ws.Cells(y + 3, x)).Interior.ColorIndex = 6
                        End If
                        val = ""
                    Next y
                End If
            Next x
        Next ws
        Application.ScreenUpdating = True
    End Sub
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Highlight cell based on values

    I think that the value 34115 should be 3415, perfect it should be 3415 right.

    I will check code let u know soon, thanks a ton for your valuable time.

  4. #4
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Highlight cell based on values

    code is working perfect with 3415 this criteria (4 len), is it possible that it can work for more then 4 len or less than 4 len criteria too

    thx

  5. #5
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,249
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Highlight cell based on values

    The macro will work with any length as long as the values in AU1:BF1 match the values you want to highlight.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  6. #6
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Highlight cell based on values

    Hello sir,

    Example in AU1 value is 34156 instead of 3415 rows are not getting highlight.

  7. #7
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,249
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Highlight cell based on values

    Try changing
    Code:
     xlWhole
    to
    Code:
    xlPart
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  8. #8
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Highlight cell based on values

    thats perfect code thanks a lot its working for more than 4 numbers but not working for less than 4 numbers.

  9. #9
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,249
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Highlight cell based on values

    If the numbers in "AU1:BF1" are less than 4 characters while the numbers in your data are always 4 characters then it's hard to get a match.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

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
  •