IF and Conditonal Formatting

[prkhan56]

Hello All,
I am using Office 2003 and have the following problem:


I have a worksheet in which I wish to have the word "check" in
column A if the date in Column B exceeds by 30 days..and change the
colour of Column A to Red

I tried using the following in A2

=IF(AND(ISNUMBER($b2),$b2 < (TODAY() + 29)),"check","")

What am I doing wrong?

Can anybody give me a clue?

TIA
Rashid Khan

 

[Jerry W. Lewis]

Change < to >

Jerry

 

[prkhan56]

Thanks Jerry,
I get the 'check'.. but the colour of the cell does not change to
Red... I have used CF...

can u give me a clue.. how to change the colour to red?

Rashid

 

[Jerry W. Lewis]

You don't say what you have attempted in conditional formatting.

- Format|Conditional Formatting
- change Condition 1 to "Formula Is"
- put in a formula like
=IF(AND(ISNUMBER($b2),$b2 > (TODAY() + 29)),TRUE)
note that the leading = is required, and that the formula must return
TRUE or FALSE, with TRUE indicating that you want to apply a special format.
- Click the Format button and on the Font tab, select the Color red.

Jerry

 

[prkhan56]

Hello Jerry
My intention is to have the value 'check' in Column A with the Word
'check' in Red Background when a certain date exceeds by a month in
Column B

This is what I have put in CF
=IF(AND(ISNUMBER($B2),$B2 > (TODAY() + 29)),"check","")

I wish to have the colour of the Cell to Change to Red when 'check' is
shown.

You suggested the following formula..but it will not give me the word
'check' in the cell

=IF(AND(ISNUMBER($b2),$b2 > (TODAY() + 29)),TRUE)
Hope I am clear now

Can u give me a clue?

Thanks for your time and help
Rashid

 

[Jerry W. Lewis]

I understand that under a certain condition, you want two separate
actions to occur:

1. the word "check" to appear in a cell
2. the background color of tha cell to change to red

You have a cell formula that accomplishes action 1.

My second reply gave you a conditional formatting (assuming that you
have Excel 97 or later) formula that will allow you to accomplish action 2.

My best guess is that you are trying to use the conditional formatting
formula as a cell formula.

The only new piece of information I see in your post is that you want
the cell background to be red rather than the cell text. The
instructions I gave my the second reply (click Format button and select
color on the Font tab) would change the text color rather than the
background color. Instead, when you click the format button, select
background color at the Patterns tab.

Jerry

 

[prkhan56]

Hello Jerry

I understand that under a certain condition, you want two separate
actions to occur:

1. the word "check" to appear in a cell
2. the background color of tha cell to change to red

Yes the above is my requirement

You have a cell formula that accomplishes action 1.

My second reply gave you a conditional formatting (assuming that you
have Excel 97 or later) formula that will allow you to accomplish action 2.

My best guess is that you are trying to use the conditional formatting
formula as a cell formula.

I have put CF in 'Formula is' =IF(AND(ISNUMBER($B2),$B2 > (TODAY() +
29)),"check","") this gives me check

In second condition I have put
=IF(AND(ISNUMBER($B2),$B2 > (TODAY() + 29)),TRUE) .. this gives me
background colour but not the word 'check'

I need both condition in one formula. I tried
=IF(AND(ISNUMBER($B2),$B2 > (TODAY() + 29)),"check","",TRUE)--> this
gives an error to me

Can you give me a clue please?
Thansk for your time and help
Rashid

 

[Jerry W. Lewis]

A conditional formatting formula must return TRUE or FALSE. If TRUE,
then the formatting will be applied (no conditional formatting will be
applied if it returns anything else). Displaying the word "check" is
not formatting; it should be returned by the cell function.

Jerry

 

[prkhan56]

Hello Jerry,
Thank you very much. I got your point and tried it.. and got the
desired result

Thanks once again for your time and help

Rashid

A conditional formatting formula must return TRUE or FALSE. If TRUE,

 

[Jerry W. Lewis]

You're welcome. Glad it helped.

Jerry