Similar formula not working on different selections

2H9M9P1

New Member
Joined
Nov 3, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I have some columns where I have done a =TODAY()-14, TODAY()-7, TODAY() gradated red-yellow-white scale (column D) to show how long ago something was received or requested.
This is working fine.
I have also selected column D to conditionally format as WHITE FILL (so it looks blank) if column E is NOT BLANK. (The idea being that if I have data/text in column E, the work has been actioned, so the item in D is not longer outstanding and does not need to be red/yellow).
This is working fine on THIS selection. As you can see the single dot (as a test) in column E has turned item in D (23/09/2020) which was the previously red, white.
1.png


I used the following formula on column D, as well as the gradated red-yellow urgency scale.
2.png


HERE IS THE PROBLEM.
Further along in the worksheet, I want to do the same thing with columns T and W.

3.png



Here is the formula I'm using on column T (as well as the colour scale as before).
So why is column T not going white when there is data in column W, as it worked on the previous selected.
4.png


I have a feeling it's about conflicting formatting, but why is it working once, but not again?
I'm no pro whatsoever, so please explain simply :)

Thank you
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Do you have multiple rules on column T? Only one can be valid at any one time. Which one is returned can vary depending on whether or not 'Stop if True' has been checked.
 
Upvote 0
Do you have multiple rules on column T? Only one can be valid at any one time. Which one is returned can vary depending on whether or not 'Stop if True' has been checked.
I do have multiple rules on T.
But the same multiple rules I have on column D.
 
Upvote 0
Then the only possible reason is that a different rule is being applied, either due to the rules being in a different order or a different one also being true and taking precedence over the white background rule.

If two (or more) rules can be true and need to be applied together then they need to be combined into one single rule.

If only the white background rule needs to be applied as a priority over the others, then that needs to be at the top of the list with the 'Stop if True' box checked.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,214,788
Messages
6,121,575
Members
449,039
Latest member
Arbind kumar

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