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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,908
Office Version
  1. 365
Platform
  1. Windows
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.
 

2H9M9P1

New Member
Joined
Nov 3, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,908
Office Version
  1. 365
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,122,437
Messages
5,596,130
Members
414,043
Latest member
thomas Stein

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
Top