formula needed for condition 2 of conditional formating

**Don Juan** · 09-10-2006, 04:50 PM

I have created a conditional format that highlights a series of cells red when 2 dates are 14 days apart. =OR(($H$22-$I$22+1)>14,($I$22-$H$22+1)>14)

However as is now the formula is making all of the empty cells red. I'm trying to prevent this with condition 2. But I dunno what that would be. I want the empty cells, f12:i50 left alone unless there are dates in the h and i columns that are 14 days apart.

condition 1 = make cell f 12:I12 red if h12 and i12 are 14 days apart (example)
condition 2= ignore condition 1 if h column has no date (i column will always have todays date, h column is inputed by user).

**VBA Noob** · 09-10-2006, 05:59 PM

Hi,

Select columns F to I. Then go to Format > conditional formats and enter

=IF(AND($H1<>"",$I1<>""),OR(($H1-$I1+1)>14,($I1-$H1+1)>14),"")

It looks down from row one and if criteria is meet changes only those cells.

Let me know if it works

VBA Noob

**Don Juan** · 09-10-2006, 09:13 PM

formula worked great. Only prob is (and it was doing it before too) when I test the formula and say it removes a red highlight from the 3 side by side cells only 2 of the cells goes back to normal and 1 stays red, until I hit f9 (which is what generates my watch bill) I guess it refreshes it or something but anyhow, how do i get it to automatically refresh itself so all the red goes away or vice versa (all the red appears)?