Consulting

Results 1 to 3 of 3

Thread: match cells based on a string provided

  1. #1

    match cells based on a string provided

    Hi,

    I need help to create a vba function that helps to match data.

    I got a string that contains invoice no, company name, type and date.

    I need to create a function to match the string with a list of invoice no. from a column in another sheet.

    If the string and invoice no. are matched, it will then proceed to match the company name, type and date from the same row as the invoice no.


    The box will turn green if the information matches the string
    The box will turn red if the information does not match the string

    An example of the result. Sample.xlsx

    Thank you

  2. #2
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    Option Explicit
    
    
    Sub test()
        Dim dic As Object
        Dim v
        Dim k As Long
        Dim r As Range
        Dim c As Range
        Dim s
        Dim inv As String
        
        Set dic = CreateObject("scripting.dictionary")
        
        v = Sheets("sheet2").Cells(1).CurrentRegion
        
        For k = 2 To UBound(v)
            dic(CStr(v(k, 1))) = Array(v(k, 2), v(k, 3), v(k, 4))
        Next
        
        Set r = Sheets("sheet1").Cells(1).CurrentRegion.Columns(3)
        Set r = r.Resize(r.Rows.Count - 1).Offset(1)
        
        r.Offset(, 4).Resize(, 4).Interior.Color = vbGreen
        
       For Each c In r.Cells
            s = Split(c.Value & c.Offset(, 1).Value)
            inv = s(2)
            If Not dic.exists(inv) Then
                 c.Offset(, 4).Resize(, 4).Interior.Color = vbRed
            Else
                If dic(inv)(0) <> s(0) Then c.Offset(, 5).Interior.Color = vbRed
                If dic(inv)(1) <> s(1) Then c.Offset(, 6).Interior.Color = vbRed
                If dic(inv)(2) <> DateValue(s(4)) Then c.Offset(, 7).Interior.Color = vbRed
            End If
       Next
        
    End Sub

  3. #3
    Thanks! It works perfectly

Posting Permissions

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