2 conditional format rules same cell

[StartingOut]

I have this conditional format based on a formula but my formula is not working. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have a cell that turns red if another cell is less than a value, which is working. but then a second rule for the same cell with the IF statement conditional formatting. The formula should consider other cells and values and then change the cell to red over riding the original condition which set the cell color to red or green. so cell B should Red if cell C is less then 5 but then if the IF statement is true change the color to the appropriate color red or green.<o></o>
<o></o>
="IF 'Annual Y2'!$J$5<10 AND ('Annual Y2'!J$5-'Annual Y2'!$K$5)<2"<o></o>

 

[jfish1288]

try changing your formula to

=IF(AND('Annual Y2'!$J$5<10, ('Annual Y2'!J$5-'Annual Y2'!$K$5)<2))

 

[jfish1288]

actually for conditional formating you do not need the if, just

=AND('Annual Y2'!$J$5<10, ('Annual Y2'!J$5-'Annual Y2'!$K$5)<2)

 

[StartingOut]

I tried =AND('Annual Y2'!$J$5<10, ('Annual Y2'!J$5-'Annual Y2'!$K$5)<2)

but get the error "you cannot use references to other worksheets or workbooks for Conditional Formatting criteria"

 

[jfish1288]

Oh yeah thats right. What I would do is in some unused cells in the worksheet you are working on, set those cells equal to your reference cells and then use these new reference cells in your formula. You can hide these new ref cells if you want by formating them as custom ;;;

for example in the sheet that you want to do the conditional formatting set cell J5='Annual Y2'!$J$5 and cell K5='Annual Y2'!$K$5

then change your conditional formating formula to
=AND($J$5<10, ($J$5-$K$5)<2)

To hide J5 and K5:
Select the cells. Go to format cells, choose custom and type ;;;

 

[StartingOut]

a quick google search tells me this will not work as my data is not all on the same sheet and conditional formatting with a formula does not work across multiple sheets or workbooks, Is there a VB script that will do what I need?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[StartingOut]

good Idea, let me try that

 

[jfish1288]

I wasn't very clear on what you are trying to do. I just noticed the error in your formula. Linking your reference cells to unused cells on the sheet you are doing the conditional formating like I described earlier does work though. In your new reference cell type "=" without the quotes then select your old reference cell and hit enter.

 

[StartingOut]

I have made the choice to go with cells Z1000 to = my data 'Annual Y2'!$J$5 and Cell Z1001 to ='Annual Y2'!$K$5<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
so Z100 now is "8" and Z1001 is now "7" my formula<o></o>
=AND($Z$1000<10, ($Z$1000-$Z$1001)<2) is not working.<o></o>
<o></o>
the first condition set Cell I3 red if cell G3 on the same sheet is less than "6" which it does the cell is now Red. the second contrition should subtract Z1000 - Z1001 which in this case is 8-7=1 my formula should now find that the condition is true as 1 is less than 2 and turn I3 green bit it does not. I must be missing something<o></o>

 

[jfish1288]

It seems that you have 2 conditional formats that are set to produce mutually exclusive outputs. If the cell is red it can not be green and vice versa. Excel chooses to default to the first condition when multiple conditions produce conflicting results. I would put your overriding condition in the first conditional format slot, and the secondary condition in the second slot.

If you want to know if the outcome of both conditions with out overriding I would suggest having one condition change the font to bold and the other change the color or something to that effect.