Highlight cell based on values

[JAVEDR]

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.

 

[mumps]

Try this macro. I think that the value 34115 should be 3415.

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

 

[JAVEDR]

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.

 

[JAVEDR]

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

 

[mumps]

The macro will work with any length as long as the values in AU1:BF1 match the values you want to highlight.

 

[JAVEDR]

Hello sir,

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

 

[mumps]

Try changing

 xlWhole

to

xlPart

 

[JAVEDR]

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

 

[mumps]

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.