Highlight cell based on values

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
The macro will work with any length as long as the values in AU1:BF1 match the values you want to highlight.
 
Upvote 0
view
view
view
Hello sir,

Example in AU1 value is 34156 instead of 3415 rows are not getting highlight.
 
Upvote 0
Try changing
Code:
 xlWhole
to
Code:
xlPart
 
Upvote 0
thats perfect code thanks a lot its working for more than 4 numbers but not working for less than 4 numbers.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top