hello
I search for macro to highlighted font for some unmatched items into column A and compare with column B for each sheet .
so should match adjacent cells into column A with adjacent cells into column B . if there are unmatched items then should highlighted by red .
the result should be into column A for sheets 1,2,3 . I put desired result in result sheet1,2,3
thanks
Last edited by tubrak; 03-13-2022 at 06:20 AM.
Hello, try this !
According to your attachment a VBA demonstration for starters (v3) :
PHP Code:Sub Demo1()
Const D = " "
Dim Ws As Worksheet, V, R&, S&, W, L&
For Each Ws In Worksheets
V = Ws.[A1].CurrentRegion.Value2
With Ws.Range("A2:A" & UBound(V)).Font: .Bold = False: .ColorIndex = xlAutomatic: End With
For R = 2 To UBound(V)
S = 1
V(R, 2) = D & V(R, 2) & D
For Each W In Split(V(R, 1))
L = Len(W)
If InStr(V(R, 2), D & W & D) = 0 Then With Ws.Cells(R, 1).Characters(S, L).Font: .Bold = True: .Color = vbRed: End With
S = S + L + 1
Next W, R, Ws
End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Last edited by Marc L; 03-14-2022 at 06:29 AM. Reason: optimization …
thanks for your code.
actually I don't find the wrong result in sheet1. forgive me can you show me?
by the way there is problem in cells A6 into sheet1 contains MN and not existed in column B , then should highlighted,but the code doesn't do that.
Re: mark color for not matched items for adjacent cells based on another adjacent cells
Originally Posted by tubrak
Wrong ‼ And by the way this is your error in 'result sheet1' …
Re: mark color for not matched items for adjacent cells based on another adjacent cells
Or do you just compare a cell with only another cell in the same row and not with all the column (as you wrote cells) ?
my topic is adjacent cells , also explanation . this means a2=b2,a3=b3 and so on the rest of cells .
I don't think to be this word CELLS will make completely confusing for you . sorry !as you wrote cells) ?
yes . I had to use this sentence to understand my idea .do you just compare a cell with only another cell in the same row
Re: mark color for not matched items for adjacent cells based on another adjacent cells
According to this clear explanation try the updated demonstration in post #2 …
thanks again.
I note this item K/L in column A ,if add L in column A ,then it doesn't highlight it . the code considers it is existed . K/L should be whole item is different ,for L .
last thing. I try understanding your code and mod your code when change item in column B and match with item has already highlighted into column A to delete the color instead of do that manually ,but I don't succeed so for.
I mod a simple in your code .
Please Login or Register to view this content.
Re: mark color for not matched items for adjacent cells based on another adjacent cells
Did not you think about the easy mod just adding a leading space like in the updated post #2 …
why when you put the space into variable then the code works without any problem ?
Re: mark color for not matched items for adjacent cells based on another adjacent cells
Just according to the Logic to compare words space delimited …
thanks for your help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks