PDA

View Full Version : Multiple conditional formatting to the same range



DavidMontoya
06-02-2019, 04:05 AM
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 portion do not work.

Thanks in advance for your help.

Bob Phillips
06-02-2019, 07:48 AM
Those formulas make absolutely no sense whatsoever to me.

Can you explain in plain language what conditions you want to satisfy?

DavidMontoya
06-02-2019, 12:56 PM
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="")))


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.