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>
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,096
Messages
6,105,940
Members
447,985
Latest member
gigi11

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