Turning Cell red using Conditional Format (if Possible)

    Can you use an Array in Conditional formatting.


    I have a situation where I have a name in Column O with a list of names in Column M.


    If the name in Column O appears in Column M, look at Column J. If the letter I, for incomplete, appears in Column J, in Column P, next to the name, turn the cell color Red. Otherwise, do nothing.


    I don't know how to do multiple criteria using Conditional formatting. Can this be done with this problem?


    If at all possible, I do not want to use VBA if possible. It is for someone else here at work.




    Thanks for your help.


    I included psuedo code on the spreadsheet (P6).

    sorry to be blunt, but no, it is not possible


    your names do not tie up, they are spelled inconsistently; your scorces have duplicate names; and you have surnames only in your target ranges but full names in your scources


    I'd recommend splitting your scource column into 2, or 4 columns, each with a name in it.


    I'd further recommend then making your target list MATCH exactly those names, full names......


    this might give us somethign to work better with, using a combination of =find, =search, =match, and =AND in your conditional formatting


    as it is, VBA is your only hope


    hope this helps, albeit negatively
    Chris
    :)

    I didn't look at your file, but conceptually -my quick & dirty solution:
    Just to make it easier I moved your column J to column N so we could use a vlookup - other solution (leaving it in col j) is to use match/index instread of vlookup


    Put this in column P
    =VLOOKUP(O19,M$1:N$200,2,FALSE)="I"
    if it equals TRUE then conditional format=red

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login