Conditional Format

Jester 1978

New Member
Joined
Sep 15, 2002
Messages
48
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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?
 

Jester 1978

New Member
Joined
Sep 15, 2002
Messages
48
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

Active Member
Joined
Aug 12, 2002
Messages
382

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

New Member
Joined
Sep 15, 2002
Messages
48

ADVERTISEMENT

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
 

Jester 1978

New Member
Joined
Sep 15, 2002
Messages
48
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

Threads
1,144,768
Messages
5,726,179
Members
422,660
Latest member
mrsteele

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top