this works but than it stopped other conditional formatting which turns Red if it's reached to expiry dates currently I have formula =A1<today().
If you have multiple conditional formatting rules applied to the same cell, you must make some decisions.
It is possible to have a cell formatted with multiple conditional formatting rules on the same cell, as long as they do not interfere with each other.
For example, you cannot have one that says turn the text red, and the other that says turn the text blue, and expect both to work (since they are both coloring the text).
However, you could have one that says turn the text red, and the other that says make the text bold and italic, and apply both.
If you have multiple CF rules trying to affect the same formatting (i.e. shading of the cell), then you must order them and determine which rule takes precedence over the other.
If you go to your cell, and select Conditional Formatting, and pick "Manage Rules", you will see all the rules applied to that cell.
Note that you can change the order by moving rules up and down.
And also notice the "Stop if True" checkbox on the far right of every rule. If you have that checked, if that condition is met, it will not check anything underneath it.
So if you want to be able to apply multiple formats to a single cell, you will probably want to make sure that all those boxes are NOT checked.
Additionally, sometimes you might need to modify your rules to make sure that the different rules are exclusive (i.e. exclude the conditions of the other).
You can usually do this with AND and/or OR functions.
