SOLVED

conditional formatting not working in every cell

Copper Contributor

So I have conditional formatting where a range will be filled in yellow if the value equals zero. No idea why, but cells b29:b40 and f29:f40 are not getting formatted. Is there some other formatting that is in place that can be impacting this? I looked carefully at these cells and I am not noticing what could be causing this. The weird thing is when I start messing with the formula, it starts filling in some of the cells. For example, I changed the formula to sum(f35:f40)=0 and cells f36:f40 became yellow.

 

mnirx_0-1660853467810.png

 

8 Replies
Just noticed if I do >= (equal to greater than) it all turns yellow.. I manually entered zeroes in there. So no way it is some small decimal number that is hiding itself. Bad thing is - it would be a pain to rebuild this workbook.
best response confirmed by mnirx (Copper Contributor)
Solution

@mnirx 

conditional formatting.JPG

This is the rule for conditional formatting of the cells with value zero:

=A1=0

This is the range to which formatting is applied:

=$A$1:$F$30

Maybe this is helpful for your task.

 

You're a genius. Still annoying that the formatting is not taking place, but what I ended up doing was for each column, if the total is zero, then that range of cells will be yellow. Before it was just the range of cells needing to be zero.

Super frustrating that this was even an issue. Thank you so much. Your reply helped me figure out another workable solution.
Welp, I was wrong. Now when I enter a value some of the cells still stay yellow, and this other "solution" did not work for column B. I guess something is wrong with this workbook. I cannot see any other cause other than an inherent problem with the workbook itself.

@mnirx 

Can you attach a screenshot which shows the rule(s) for conditional formatting you currently work with along with the applies to ranges and the results in the worksheet (with row and column descriptions like the screenshot in my earlier reply)? 

@OliverScheurich 

 

For context, each column (C thru F) represents a week for a month. So column C will be completed on first week, D on second week, etc. My original post included column B, but for now I am leaving that one out as I am just currently focusing on these four. Each screen shot here contains C, D, E, and F.

 

As you can tell I changed the formula from my original post. Weird thing is everything was yellow in column F originally. I cleared out the conditional formatting, but entered it again to provide the screen shot. Now cell F28 stays white, along with B28. In addition, the range in each selection does not turn white when a value is enter (second screen shot). Bottom row in second screen shot is row 42.

 

mnirx_0-1660926553852.png

 

mnirx_1-1660926881542.png

 

@mnirx 

cf.JPG

Unfortunately I can only guess what you exactly want to do. My assumption is that you have to adapt the rules for conditional formatting to the applies to ranges as shown in the screenshot.

Applies to range:  =$F$25:$F$40     rule: =F25=0

Applies to range:  =$E$25:$E$40    rule: =E25=0

and so on.

The cell the rule refers to has to be the top left cell of the applies to range.

Do you mean, for example, instead of the condition of =c42=0 for range =$C$25:$C$40 - the condition should be =d42=0?

If I am not understanding, that is fine. It seems I found the solution. I changed the condition to, using column C as an example, to =$c$42=0

And it resolved the issue. Something is still very buggy about this. Either way - I'm crossing my fingers that this works as I continue to test it out.

Thank you so much for your replies. Greatly appreciate you taking the time.
1 best response

Accepted Solutions
best response confirmed by mnirx (Copper Contributor)
Solution

@mnirx 

conditional formatting.JPG

This is the rule for conditional formatting of the cells with value zero:

=A1=0

This is the range to which formatting is applied:

=$A$1:$F$30

Maybe this is helpful for your task.

 

View solution in original post