Conditional Formatting Formulas

Hi All,

Aim/ Object — We have two columns - B and C, the column 'B' tells about the action name (what action is happening or has done), and the column 'C' tells about the time of the action.

I want to put a condition formatting in the column 'C', as follows:
If column 'B' has 'Welcome' as text/ value, then in the column 'C' (next cell), the condition formatting color should be in 'Green'.
If column 'B' has 'Start ' as text/ value, then in the column 'C' (next cell), the condition formatting color should be in 'Blue'.
If column 'B' has 'Goodbye' as text/ value, then in the column 'C' (next cell), the condition formatting color should be in 'Brown'.

Note — Color formatting for the cell value/ text - 'Time Take Duration' :
If the 'Time Take Duration' is less than 1:15:00, then there should be 'Yellow' color in the next cell (column C).
If the 'Time Take Duration' is greater than 1:15:00, then there should be 'Red' color in the next cell (column C).

All this need in the Excel 2003.

Please help me out!

Thanks in advance!

Hi,

See the attached.

Incidentally it would pay to update your details. I was about to respond that with 2003 you are limited to 3 conditional formats. Then I noticed your file was a 2007/2010 file which handle many more CFs.

HTH

Hi,

As I said, with 2003 you are limited to 3 conditional formats. If any of the cells can have any of the 5 conditions you can't do it with CFs, and you'd need to resort to a VBA user defined function.

If however you are prepared to have two definitions of conditional formats, one with 3 formats for the Welcome Start Goodbye cells and the other with 2 formats for the Time cells, then just use the examples in my last file e.g. using the Formula Is option, enter the 3 formats with the following,

=B5="Goodbye"
'etc.

and the two formats using the Cell value option with the greater than or equal to choice, and less than choice

=1.25/24

HTH

If however you are prepared to have two definitions of conditional formats, one with 3 formats for the Welcome Start Goodbye cells and the other with 2 formats for the Time cells, then just use the examples in my last file e.g. using the Formula Is option, enter the 3 formats with the following,

Thanks a world dear!

Got it!

I just selected all the required cells in the column 'C', then Format Menu > Conditional Formatting

Condition 1
Formula Is  =OR($B5="Welcome",$B5="Start",$B5="Goodbye")
                             then 'Blue' Color select

Condition 2
Formula Is  =AND($B5="Time Take Duration",$C5<=1.25/24)
                             then 'Green' Color select

Condition 3
Formula Is  =AND($B5="Time Take Duration",$C5>1.25/24)
                             then 'Red' Color select

Thus if the cell of column 'B' is blank then the relative cell of column 'C' will be blank with 'no formatting'.

Please correct me if I am wrong at any point.