Conditional Formatting Based On Changing Dates

Solved/Closed
SpaceMonkees Posts 7 Registration date Wednesday July 31, 2013 Status Member Last seen December 2, 2013 - Jul 31, 2013 at 12:41 PM
SpaceMonkees Posts 7 Registration date Wednesday July 31, 2013 Status Member Last seen December 2, 2013 - Aug 5, 2013 at 07:53 AM
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 responses

russp5 Posts 4 Registration date Friday August 2, 2013 Status Member Last seen August 2, 2013 1
Aug 2, 2013 at 01:18 PM
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
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Aug 1, 2013 at 11:20 AM
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
0
SpaceMonkees Posts 7 Registration date Wednesday July 31, 2013 Status Member Last seen December 2, 2013
Aug 2, 2013 at 11:27 AM
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?
0
SpaceMonkees Posts 7 Registration date Wednesday July 31, 2013 Status Member Last seen December 2, 2013
Aug 5, 2013 at 07:53 AM
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.
0