Multiple conditional formatting to the same range

[David Montoya]

Dear,

Please help me with the following situation.

I need to have the following two conditional formatting to the same range W4 to W1004:

=IF(IF(AND($Q4=""),($W4=""),IF(AND($Q4="N/A"),($W4=""),IF(AND($Q4<>""),($W4<>"")))),"",IF(AND($Q4<>""),($W4="")))

=IF(IF(AND($M4=""),($W4=""),IF(AND($M4="N"),($W4=""),IF(AND($M4<>""),($W4<>"")))),"",IF(AND($M4<>""),($W4="")))

They both work fine independently; however, when join them together, as follows:

=IF(IF(AND($M4=""),($W4=""),IF(AND($M4="N"),($W4=""),IF(AND($M4<>""),($W4<>""),(IF(AND($Q4=""),($W4=""),IF(AND($Q4="N/A"),($W4=""),IF(AND($Q4<>""),($W4<>"")))))))),"",IF(AND($M4<>""),($W4=""),IF(AND($Q4<>""),($W4=""))))

only the first conditional formatting works, and the second do not work.

Thanks in advance for your help.

 

[sparky2205]

Hi David,
what exactly are you trying to achieve?
Looking at your formulas the ANDs aren't actually doing anything.
e.g. =IF(AND($M4=""),($W4="") If you are trying to check if M4 and W4 are blank the formula should be =IF(AND($M4="",$W4="")

 

[DanteAmor]

Formulas in conditional format should not use the IF statement, only the condition:

=AND($M4="",$W4="")


If the condition is true then the cell is highlighted

 

[David Montoya]

Dante Amor,

The purpose is to highlight in red the same row cell in column W if the result is “TRUE” when combining the data on column M with column W of the same row, and/or column Q with W.

Here is the information:
1. Cell in column M could be blank, contain “Y”, or “N”
a. If the cell in column M is blank, and cell in column W is blank, is ok
b. If the cell in column M contains “N”, and cell in column W is blank, is ok
c. Cell in column M contains “Y”, and cell in column W contains data, is ok
d. If the cell in column M contains “Y”, then cell in column W must have a data input


This portion works OK with this formula, giving the “TRUE” result:

=IF(IF(AND($M4=""),($W4=""),IF(AND($M4="N"),($W4=""),IF(AND($M4<>""),($W4<>"")))),"",IF(AND($M4<>""),($W4="")))

2. Cell in column Q could be blank, contain “N/A”, or other multiple choices
a. If the cell in column Q is blank, and cell in column W is blank, is ok
b. If the cell in column Q contains “N/A”, and cell in column W is blank, is ok
c. Cell in column Q contains “ANY AVAILABLE CHOICE”, and cell in column W contains data, is ok
d. If the cell in column Q contains “ANY AVAILABLE CHOICE”, then cell in column W must have a data input


This portion works OK with this formula, giving the “TRUE” result:

=IF(IF(AND($Q4=""),($W4=""),IF(AND($Q4="N/A"),($W4=""),IF(AND($Q4<>""),($W4<>"")))),"",IF(AND($Q4<>""),($W4="")))

The above two conditional formatting work fine when set independent .

I have been trying to put the two above formulas together to have a single conditional formatting rule, and it only works for one of the portions, either the above “1.” or if I invert the sequence, it works for the above portion “2.”

Here is the formula I put together:

=IF(IF(AND($M4=""),($W4=""),IF(AND($M4="N"),($W4=""),IF(AND($M4<>""),($W4<>""),IF(AND($Q4=""),($W4=""),IF(AND($Q4="N/A"),($W4=""),IF(AND($Q4<>""),($W4<>""))))))),"",IF(AND($M4<>""),($W4=""),IF(AND($Q4<>""),($W4=""))))

This formula is to be included into a macro; I have seen other VBA macro options that may accomplish the same, but I have not been able to get even close.


Thanks a lot.

 

[DanteAmor]

Try this

Formula 1:

=OR([COLOR=#a52a2a]AND($M4="",$Q4="")[/COLOR],[COLOR=#008000]AND($M4="N",$Q4="")[/COLOR],[COLOR=#0000ff]AND($M4="Y",$Q4<>"")[/COLOR])

Formula 2:

=OR([COLOR=#b22222]AND($Q4="",$W4="")[/COLOR],[COLOR=#008000]AND($Q4="N/A",$W4="")[/COLOR],[COLOR=#0000ff]AND($Q4<>"",$W4<>"")[/COLOR])

Formula 1 and 2:

=OR([COLOR=#008000]OR(AND($M4="",$Q4=""),AND($M4="N",$Q4=""),AND($M4="Y",$Q4<>""))[/COLOR],[COLOR=#0000ff]OR(AND($Q4="",$W4=""),AND($Q4="N/A",$W4=""),AND($Q4<>"",$W4<>""))[/COLOR])