Conditional Formatting Based On Changing Dates [Solved/Closed]

Report
Posts
7
Registration date
Wednesday July 31, 2013
Status
Member
Last seen
December 2, 2013
-
Posts
7
Registration date
Wednesday July 31, 2013
Status
Member
Last seen
December 2, 2013
-
I am trying to keep on top of dates that are changed weekly based on last weeks dates, but i need to highlight the changed cells only.

Example:

A1 = Forecast Date
B1 = Previous Weeks Date

A1 changes daily - it is given to us by a supplier who generates their dates from a large list
B1 is the cell i want to highlight if it is different from A1.

Now my first thought was simple conditional formatting, so i highlighted column A & B and set the rule to highlight duplicates. This of course checks across all the cells and sees the same date and therefore all the cells got highlighted.

So essentially want to set the rule to compare A1 with B1 only (then A2 with C2, A300 with C300 etc) but not look for duplicates between A1 and C300.

I assume i could highlight each set of cells and set conditional formatting, but assume there is a simple bit of formula i could use?

My first thought was to use the $ sign and fix the cell references (absolute reference isnt it?) but wont give me the option to search for duplicates using formulas so assume i have to write my own formula?

Essentially i want to say if the 2 cells are next to each other, check for a change, highlight if true, ignore if false but has to ignore any other date within the workbook.

Im sure its simple, but dont know enough to do it successfully! any help will be appreciated!

4 replies

Posts
4
Registration date
Friday August 2, 2013
Status
Member
Last seen
August 2, 2013
1
In column C you can create a formula to check if cells on column A and B are the same:
=IF(A1=B1,TRUE,FALSE)
Now you can create the conditional formatting on that column to highlight changes.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
Hi SpaceMonkees,

Set conditional format for row 1, drag the formatted cells down, and select format only from the options square that appears when you are done dragging.

Best regards,
Trowa
Posts
7
Registration date
Wednesday July 31, 2013
Status
Member
Last seen
December 2, 2013

No unfortunately that doesn't work as it still checks for duplicate cells within the selected cells.

i.e B1 & B10 are the same date so has highlighted them as duplicates, but i want it to only check B1 against A1, B10 against A10.

Thanks for answering though! Any other thoughts?
Posts
7
Registration date
Wednesday July 31, 2013
Status
Member
Last seen
December 2, 2013

I knew I was over complicating things!

Thanks Russp5 - its certainly a work around so just need to tweak it to make it work well within my sheet.