Compare 2 columns and return fill red if it is different

Question asked by user @ Excel forum:- 

How to compare 2 columns through conditional formatting and return fill red if it is different ?


I want to compare 2 columns( A column and B columns ) if data in columns B is different i want to change to red color.

I ask you a simple version for my problem but the problem is little more difficult I want to compare a list of columns A and B for example P.12242 with R1112242900 ; 12242 is common number.


image 1


In the above mentioned query user want to compare two columns, in which he wants where the cells are not same in the both column that should be highlighted with the Red color.

Our Excel expert has given very nice reply and resolve his problem. Let’s see how we can do compare two columns through Conditional Formatting.

  • Select the both columns Column A and B.
  • Then click on “Conditional Formatting” drop down list will appear, where we have to select “Manage Rules”  dialog box will get open.
  • Where we have to click on “New Rule”, New Formatting Rule dialog box will get appear, click on Use a formula to determine which cells to format.
  • =MID($A2,3,5)<>MID($B2,4,5)
  • Click on Format and select the Red color.

image 2


  • Click on ok.
  • Then Click on Apply, and ok.




If the cells will not matched in both of the columns then it will get highlighted with the Red color.

image 5

This the way we can use the Conditional Formatting to compare the two columns with each other.


Main Page

Conditional Format Based on Dates

Find Occurrence of Text in a Column

How to Highlight a row on the basis of Cell

Compare 2 Columns and Return Fill Red if is different

How to check the row and then highlight the first cell of the row

Highlight Cells Tomorrow Excluding Weekend

Conditional Formatting to Mark Dates on a Calendar

How to apply Conditional Formatting  in a Cell before a Particular Character

Highlight the Top 10 Sales through Conditional Formatting

Conditional Formatting for Pivot Tables

Conditional Format Between First and Last Non-Blank Cells




Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.