Conditional formatting with a twist

Nathalie

New Member
Joined
May 22, 2002
Messages
48
I have it half figured out by using other great posts on this site!

I keep today's date ( =today() ) in cell A1 but I keep it hidden.

I used conditional formatting on column K:K so that if the date found in cell is >= A1 it would change cell background to red.

My goal is that I would like users to be aware that a document is due, but instead of just turning the cell red I would like whole row to turn red! How could I achieve this???

(Also as a side question excel tells me to enter a date as dd/mm/yy but my worksheet only shows the correct date (04-mar-97) if I type in yy/mm/dd??? Will all the users have tihssame problem, what is the problem?)

Thanks ahead of time,
Nathalie
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Select the row (say row 2) for conditional formatting. Use the formula is option. Enter

=$K2>=Today()

Note that the Column (K) is Absolute, but the row number (2) is relative. you may copy the formatting down as needed. Also, the function Today() can be used directly in the conditional formatting. No need for the hidden cell.
 
Upvote 0
Thank you lenze :smile: for the quick reponse, it wirks great! I learn something new everyday. Any ideas about my second question?
 
Upvote 0
O.K found a problem! Before I was trying the formula on a test sheet. The formatting works fine except that the database is structured such that in column k the user has to place the date document is due, once sent out the word "sent" has to be written in cell.

The formatting highlights all cells that are due and those that are marked "sent". How do I get around this problem?
 
Upvote 0
I formatted E3 based on F3 using this formula is

=AND($F3>=TODAY(),NOT(ISTEXT($F3)))

If the F3 date is greater than Today(), E3 turns red. When I type Sent into F3, the red disappears
This message was edited by lenze on 2002-08-27 08:18
 
Upvote 0
I found the error seems that excels required a semicolon between logical statements instead of comma....(excel '97)

= AND($E1>=TODAY();NOT(ISTEXT($E1)))

Thanks for all the help Lenze!
 
Upvote 0
The French version of Excel requires a semi-colon. The American/English version requires a comma.

Regards,


Mike
 
Upvote 0
The French version of Excel uses a semi-colon as a separator. The American/English version uses a comma.

Regards,


Mike
 
Upvote 0

Forum statistics

Threads
1,223,151
Messages
6,170,381
Members
452,322
Latest member
CrimsonCoure

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
Back
Top