Find Mismatched Cell Values

RedMug

New Member
Joined
Sep 17, 2014
Messages
5
I am trying to identify cells within a row that have a different value from the second cell in the row. (The first cell in the row represents the identifier) All the cells within the row should either have a blank (if there was no value linked to the identifier) or a set of RGB values separated by commas. I have tried a variety of MATCH, EXACT, Special - duplicate rows, type formulas but either the commas within the cells or the blanks are messing with the formulas. As an example in row 2, E2 and F2 are different from B2, so ideally I would like these cells to be highlighted to point out the difference. Thank you in advance to whoever can help.
Please let me know if I need to clarify more.
IdentifierBCDEFG
Cat214,12,0214,12,0214,12,0213,11,0213,11,0214,12,0
Dog148,40,68148,40,68148,40,68148,40,68148,40,68148,40,68
Mouse173,79,137173,79,137173,79,137173,79,137173,79,137173,79,137

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
Just use some conditional formatting! Or, if you want a macro because I love macros....

Code:
Sub colorize()

    Dim colStart As Integer
    Dim colEnd As Long
    
    Dim rowStart As Integer
    Dim rowEnd As Long
    
    colStart = 3
    colEnd = Cells(1, Columns.Count).End(xlToLeft).Column
    
    rowStart = 2
    rowEnd = Cells(Rows.Count, "A").End(xlUp).Row
    
    For x = rowStart To rowEnd
        For y = colStart To colEnd
        
            If Cells(x, y) <> Cells(x, 2) Then Cells(x, y).Interior.Color = vbRed
        
        Next y
    Next x
End Sub
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,184
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Select all the cells from C2 to G whatever (in your posted data whatever would be 4, so C2:G4).
On the Data menu choose Conditional Formatting>New Rule>Use Formula.
In the formula box enter: =C2<>$B2
Set the format to fill unmatched cells with your desired color.
Click OK.
 

RedMug

New Member
Joined
Sep 17, 2014
Messages
5
Just use some conditional formatting! Or, if you want a macro because I love macros....

Code:
Sub colorize()

    Dim colStart As Integer
    Dim colEnd As Long
    
    Dim rowStart As Integer
    Dim rowEnd As Long
    
    colStart = 3
    colEnd = Cells(1, Columns.Count).End(xlToLeft).Column
    
    rowStart = 2
    rowEnd = Cells(Rows.Count, "A").End(xlUp).Row
    
    For x = rowStart To rowEnd
        For y = colStart To colEnd
        
            If Cells(x, y) <> Cells(x, 2) Then Cells(x, y).Interior.Color = vbRed
        
        Next y
    Next x
End Sub



Thank you for this! I knew it had to be something simple like formatting but I couldn't get the right formula to do what I wanted. I also love macros, but I am new to them and the conditional formatting did the trick.
 

RedMug

New Member
Joined
Sep 17, 2014
Messages
5

ADVERTISEMENT

Thank you JoeMo, this is what I was looking for!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,492
Messages
5,523,260
Members
409,506
Latest member
reneekeane

This Week's Hot Topics

Top