MoonPoint Support Logo


Shop Amazon Warehouse Deals - Deep Discounts on Open-box and Used ProductsAmazon Warehouse Deals

Advanced Search
Sun Mon Tue Wed Thu Fri Sat

Tue, Sep 10, 2013 9:37 pm

Changing the background color for a cell in Excel for Unequal Values

To compare whether two cells contain equal values, whether numbers or text, in Excel you can use <>. E.g., to take some action, such as changing the background color of a cell, C3, if it does contain the same data as another cell, B3, I could use a formula of =B3<>C3. E.g., Suppose I have two columns, B and C, containing values I wish to compare and wish to change the background color of a cell in column C to pink whenever it does not match the value in the adjacent B cell, so that I can easily scan the spreadsheet to find all instances where the values differ. The first row is a heading row and the second row is blank with the data starting in the third row as shown below:

1 Sensor1st Reading2nd ReadingChecked
3 Alpha32329/3/13
4 Beta35 379/3/13
5 Delta 36 339/3/13
6 Epsilon 38 349/3/13
7 Gamma38389/3/13

In Microsoft Excel 2008 for Mac, to have the background color of cells in column C change to pink whenever the value does not match the adjacent value in column B, I can click on cell C3, the first cell in column C to contain data, then click on Format then select Conditional Formatting. I can then for Condition 1, change "Cell Value Is" to "Forumula Is" and use =B3<>C3 for the formula. I can then click on the Format button, select Patterns, and pick the color I want for the cell background and click on OK.

Excel - pink conditional formatting

If I click on OK again, the conditional formatting is set for cell C3. To copy it to all the cells below in the column, I can hit Command-C on a Mac (the equivalent to Control-C on a Microsoft Windows system) to copy the cell. I can then click on the cell immediately below and drag downwards until all of the cell from C4 to the bottom of the column are highlighted. I can then click on Edit and select Paste Special. If I then select Formats from the Paste section and click on OK, the conditional formatting will be applied to all cells with data in column C so that any that don't match their adjacent neighbor in column B will have a pink background.

Excel - Paste Special Format

[/os/windows/office/excel] permanent link

Once You Know, You Newegg AliExpress by

Shop Amazon Local - Subscribe to Deals in Your Neighborhood

Valid HTML 4.01 Transitional

Privacy Policy   Contact

Blosxom logo