Conditional Formatting

[rhi2424]

I was wondering if someone could help me. I am using windown excel 2003. I have a live waiting time and a date first appointment is made. The live waiting time calculates a number based on a formula and gives back a value. When this value is between 12 and 17 I have formatted the cell to go orange. When the cell is =>18 I have formatted the cell to go red.

However, I am trying to find a way to make the live waiting time (column J) to ignore this conditional formatting if there is any value in the corresponding K cell (always be a date or nothing at all). For example if K3 had a date in it then J3 regardless of the value to not change colour.

For example if the waiting time was 12 weeks it would go orange, however once a date had been inputted in to the K column it would remove this orange/red formatting and remain colourless. I want it to do it for each respective cell so if value is in K3 then that impacts J3, if value is in K4, that impacts J4 and on......

I hope it makes sesnse. Is this possible? If so could anyone please tell me how? It would be greatly appreciated.

 

[bbott]

Welcome to the board.

You should be able to use an AND function with your current CF statement:

=AND(K3="", your current statement here)

 

[Special-K99]

=(AND(K3="",J3 >= 12,J3 <= 17))
fiormat as orange
=(AND(K3="",J3 >= 18))
format as red

 

[rhi2424]

Thanks for your speedy replies.

I think that might of worked except for the fact that i dont want it to use K3 for each one - I need it to use k4 when evaluating cell j4, k5 when evaluating j5, k6 when evaluating j6 and so on (for all the rows in k and j column)..... is that possible or not?

 

[rhi2424]

Solved the issue. Sorry to have an add on question but if you want to also add another criteria before it changes colour, for example cell I includes the word treatment or TWL is that possible? And if so how do you do it?

Thanks.

 

[rhi2424]

Sorry to have an add on question but if you want to also add another criteria before it changes colour, for example cell I includes the word treatment or TWL is that possible? And if so how do you do it?

Thanks.

 

[bbott]

Can you post your current formula please?

 

[rhi2424]

I've used SpecialK-99 reply of

=(AND(K3="",J3 >= 12,J3 <= 17))
format as orange
=(AND(K3="",J3 >= 18))
format as red

This has worked for my original question. Just was wondering whether you can add on a condition about another value including text for it to change colour the text in column I should say "treatment" or "TWL".

 

[bbott]

Try:

=(AND(OR(I3="treatment",I3="TWL"),K3="",J3 >= 12,J3 <= 17))

 

[rhi2424]

That works thanks - but i need it to accept TWL or treatment in t he cell along with other text - not sure if thats a wildcard or something else? So if it had TWL (samantha) it would still turn orange as long as treatment or TWL is also written in the cell....

Does that make sense?