Consulting

Results 1 to 3 of 3

Thread: Multiple conditional formatting to the same range

  1. #1

    Multiple conditional formatting to the same range

    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.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    Those formulas make absolutely no sense whatsoever to me.

    Can you explain in plain language what conditions you want to satisfy?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •