Basic Conditional Formatting

David0079

New Member
Joined
Jul 26, 2007
Messages
44
Mornin all,

I have two columns of data, E & F. E contains dates and F contains status, i.e. Started, In Progress, Finished. What kind of formula would I use in conditional format to get the status box to change to red if the date in E had passed but F said In Progress?
Is it something like this....

=AND(E1:E5<TODAY(),F1:F5="In Progress") Am I being a bit simple? :biggrin:
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Conditional Formatting

Hi David

Welcome to the board

For your query

Select cell E1, and open conditional formatting window.
Under Condition, you select "formula is" instead of "cell value is"
Then type this formula:
=IF(AND((E1>0),(F1="In progress")),IF(E1<TODAY(),1,0))
and format to RED

Then copy this format to all cells in column F that need this conditional format

Hope this helps

JVN
 
Upvote 0
David0079

Welcome to the MrExcel board!

I'm going basically with DiscoPistol's solution but point out that TODAY() is a "volatile" function and can slow your sheet down considerably if you use the function a lot of time on the sheet. Therefore, if you have quite few rows of data, I suggest you put =TODAY() in an empty cell somewhere and just refer to that cell in your conditional formatting formula. That way, TODAY() only has to be calculated once each time, not once for every cell that contains it in the CF formula. Like this:

Excel Workbook
EFGH
1DateStatus01/08/2007
227/07/2007In Progress
328/07/2007Started
429/07/2007Finished
530/07/2007In Progress
631/07/2007In Progress
701/08/2007Finished
802/08/2007Started
903/08/2007In Progress
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F11. / Formula is =AND(E1<$H$1,F1="In Progress")Abc
F21. / Formula is =AND(E2<$H$1,F2="In Progress")Abc
F31. / Formula is =AND(E3<$H$1,F3="In Progress")Abc
F41. / Formula is =AND(E4<$H$1,F4="In Progress")Abc
F51. / Formula is =AND(E5<$H$1,F5="In Progress")Abc
F61. / Formula is =AND(E6<$H$1,F6="In Progress")Abc
F71. / Formula is =AND(E7<$H$1,F7="In Progress")Abc
F81. / Formula is =AND(E8<$H$1,F8="In Progress")Abc
F91. / Formula is =AND(E9<$H$1,F9="In Progress")Abc
 
Upvote 0
Good day

My Apoogies, I got bumped off the network

The formula in my post should have read:
=IF(E1>0,IF(AND(E1<TODAY(),F1="In Progress"),1,0))

Peter: Your tip on using another cell to calculate "Today()" is great . . . I never thought of that
 
Upvote 0
Okay . . . it's still not posting the complete line . . . Sorry guys
 
Upvote 0
Okay . . . it's still not posting the complete line . . . Sorry guys
That often happens when you use "<" or ">" symbols in your post and it is interpreted as HTML code. The solution is to enclose your formula between code tags like this:
{code}your formula{/code}
... only use [square brackets] instead of the {curly ones}.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

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