CODEWITHSUNDEEP
excelvba
Ferg
Ferg

Reputation: 11

Change cell color when not equal previous cell

Looking for a way to change cell color and font when the cell value differs from the value in the previous cell. Sounds easy, but there is a twist. If the value in A2 is not equal to the value in A1, I want to change the cell/font colors in cell A2, for example, fill the cell with blue and change font to white, bold. Then, as the formula/macro progresses through the rows, and eventually finds that another cell has a value that is not equal to the preceding cell, say A12 is not equal to A11, I want to change the fill/font colors for A12. I want to essentially flip-flop the fill/font colors when the not equal condition occurs. Say I set the fill/font on A1 as Blue/white-bold. When the first not equal condition occurs, I change the fill/font in A? to light-red/black-bold. Then, on the next not equal condition, change the fill/font for that cell back to the original values, Blue/white-bold.

Upvotes: 0

Views: 6318

Answers (2)

Tim Williams
Tim Williams

Reputation: 166196

  1. Select the cells to be formatted (here it's A2:A15)

  2. Add a formula-based Conditional Formatting rule using the formula:

    =MOD(SUM((A$2:A2<>A$1:A1)*1),2)=0

Adjust the formula to suit your actual range, and make sure the top cell is the "active" one (select the range from the top down, not from the bottom up) when applying the format: that way the formula will adjust correctly for the rest of the cells getting the format.

enter image description here

Upvotes: 1

Tyeler
Tyeler

Reputation: 1118

I believe the answer to your question can be found with Conditional Formatting.

Select the range you want to apply the Conditional Formatting to. On the Home tab, click on Conditional Formatting > New Rule. On the New Formatting Rule pop-up, select the rule type "Format only cells that contain" Edit the rule to have the following:

Cell Value > Not Equal To > =INDIRECT(ADDRESS(ROW()-1,COLUMN()))

Change your format to whichever you desire. Here is a picture of what you should be looking at

To make the cells automatically color themselves if they equal the above, make another rule for the column following the same steps, using the same equation, but instead set the modifer to Equal To.

Your rules in the Conditional Formatting Rules page would look like this.

Cheers!

Upvotes: 2

Related Questions