Conditional Format

[Jester 1978]

I need to set 2 conditional formats on one cell (row F).
The first one highlights the cell if the date in it, is todays date. (This date is dependant on a date put into the cell H.. infront - =IF(H2<>"",H2+30,"") basically adding 30 days) I can get this part to work, but the second condition needs to un-highlight the cell if a value is put in Cell J.. This is the bit I am stuck on. does excel not have a WHILE funtion similar to C++

Any Ideas very much appreciated

 

[Aladin Akyurek]

On 2002-10-25 05:05, Jester 1978 wrote:
I need to set 2 conditional formats on one cell (row F).
The first one highlights the cell if the date in it, is todays date. (This date is dependant on a date put into the cell H.. infront - =IF(H2<>"",H2+30,"") basically adding 30 days) I can get this part to work, but the second condition needs to un-highlight the cell if a value is put in Cell J.. This is the bit I am stuck on. does excel not have a WHILE funtion similar to C++

Any Ideas very much appreciated

Care to be a bit specific -- which cell in F and which cell in J?

 

[k209310]

Cant you use something like?

=IF(J1="","",IF(H2<>"",H2+30,"") )

 

[Jester 1978]

I need to set the conditional format in the whole of column I, so for cell I2 the format is in reference to H2 and J2, for cell I3 it is in reference to H3 and J3 and so on

 

[k209310]

I think i get what you mean. Highlight cell H if there is nothing in J else do not highlight J.

=IF(J2<>"","",IF(H2<>"",H2+30,""))

The formula does what i think you want it to do. Get back to us if its not. Put the formual in the conditional format under 'if formula is equal to'

 

[Aladin Akyurek]

On 2002-10-25 05:15, Jester 1978 wrote:
I need to set the conditional format in the whole of column I, so for cell I2 the format is in reference to H2 and J2, for cell I3 it is in reference to H3 and J3 and so on

You want to hilite I2 if the date in it equals today's date and there is nothing in J2. Right?

 

[Jester 1978]

Can't get it to work!!!!!
Let me try to simplify my explanation.
I have 3 cells in a row. in the first cell I have a date, 16-SEP-02.

In the second cell i have a formula (=IF(A1<>"",A1+30,"") which will put a date in the cell 30 days later than the first cells date.

In the 3rd cell I will put a value in, possible a date or maybe just Yes or no.

What I need is for the second cell to remain white background and black writing if either the date in it is not todays date or if there is a value in the 3rd cell.

If the date is todays datethen in needs to highlight the cell (turn it green for example) There will be no value in the 3rd cell.

I currently have:

Condition1
Formula is =(IF(C1<>"","",IF(A1<>"",A1+30,""))
Format preview - white background black writing.

Condition2
Formula is =AND(A1<>"",B1<=TODAY())
Format preview - Green background with black writing.

Sorry it is a long mail but this is winding me up now!!!!!!

Thanks for any help

 

[Aladin Akyurek]

[...]Sorry it is a long mail but this is winding me up now!!!!!!

How about answering my question?

 

[Jester 1978]

Quote..

"You want to hilite I2 if the date in it equals today's date and there is nothing in J2. Right? "

Yeah that is what I want - Please help!!!!

 

[Aladin Akyurek]

On 2002-10-25 06:08, Jester 1978 wrote:
Quote..

"You want to hilite I2 if the date in it equals today's date and there is nothing in J2. Right? "

Yeah that is what I want - Please help!!!!

(1.) Select the range of interest in I from I2 on.
(2.) Activate Format|Conditional Formatting.
(3.) Choose Formula Is for Condition 1.
(4.) Enter the following in the white box:

=(LEN(J2)=0)*(I2=TODAY())

(5.) Activate the Format button.
(6.) Choose a color from the Patterns tab.
(7.) Click OK, OK.