Conditional format when another cell is zero or more but not blank

[Garawa]

I have been playing around with many formats from posts on here but I cannot quite find a combination which works.

I want to input data into highlighted cells based on the values in other cells. The highlighting should clear when the newly inputted values are equal to or more than the values in the other cells including a zero. Eg, if a team takes a lead for 1 minute, when entering a 1 in the lead column, the minutes in the lead column should highlight to indicate a value is needed to generate the average (in this case, a 1 for 1 minute). 1 lead for 23 minutes will have the same result when 23 is input into the lead.

If the team played but did NOT take a lead, a zero lead should highlight the minutes cell which will stop highlighting when a zero is inputted. If they did not play, the cell is blank and no formatting occurs.

Any help with this would be great. Many thanks.

 

[gargamalebarbosa]

Hi, try this one please.

 

[Garawa]

Thank you. This successfully highlights the cells but they stay highlighted. This example is part of a grid which covers a lot of teams and for a entire season. I use the formatting as error checker to help prevent me incorrectly inputting into an adjacent cell so once the criteria has been met (0 minutes in the lead for no leads taken or the minutes in the lead being equal or greater than the number of leads already inputted) the highlighting should be removed to show a correct entry.

I have used this in many ways but this one is stumping me due to the zeros and blanks. Also, I have always separated my formulas with a ; (which most on here write I notice) but Excel tells me this is an error and I have to change it to a , so I presume this isn't making a difference?

 

[gargamalebarbosa]

Hi,
Sorry, english is not my native language and unfortunately i can not understand well. I hope other members can help you and solve your problem.

 

[Garawa]

No problem but thank you for replying. To avoid any confusion, the cell should highlight when:

A cell it links to has a value, be it zero or above.
The highlighted cell should go out when a 0 is entered for the cell with a zero in it, or when it is equal to or greater than a cell with a 1 or above.
There should be no highlighting if a cell is blank.

 

[gargamalebarbosa]

İs this ok ?

 

[Garawa]

I'm sorry gargamalebarbosa, I cannot see any formatting on your post to see the difference.

I have partly figured it it out. Highlighting the cells is working fine but the issue remains about extinguishing the formatting when the conditions match. I added a rule where no formatting happens if the formatted cell now matches the value of the reference cell. The only issue now is that any cell with a zero does not go out when a zero is entered into the formatted cell due to the original formatting causing it to highlight in the first place.