Conditional Format Cell Color based on Two Other Cell Values Without Blank Cells Affe

    I have a maintenance task sheet based on hours in one column and days in another.
    Each row contains the task followed by the hours and days due in their respective columns.

    Some task are based on both hours and days due.
    Some task are based on only hours due.
    Some task are based on only days due.

    I wish to change the font color of the TASK title in each row depending on the hours and days remaining. i.e.

    If the Hours OR Days Due where less than or equal to 20 and 7 respectively color RED
    If both Hours AND Days Due where more than 20 and 7 respectively color GREEN.
    The color of the TASK based only on Hours or Days must NOT be affected by a Blank Cell

    i.e.
    A B C
    TASK Hours Due Days Due
    Oil Change 20 7 RED
    Filter Change 21 8 GREEN
    Brake Check 100 6 RED
    Water Level 5 RED
    Air Pressure 35 GREEN

    So far I have conditional formatted the first column as below:

    Code
    =AND(B2>20, C2>7)

    for the GREEN

    Code
    =OR(B2<=20, C2<=7)

    for the RED

    but get stuck on the NOT(ISBLANK).
    As soon as I have either the Hours or Days Due cell blank the TASK goes RED.

    Your kind input is greatly appreciated.

    Re: Conditional Format Cell Color based on Two Other Cell Values Without Blank Cells


    Try


    =AND($B2>20,$C2>7,OR($B2<>"",$C2<>"")) for Green


    and


    =AND(OR($B2<=20,$C2<=7),$B2<>"",$C2<>"") for Red

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: Conditional Format Cell Color based on Two Other Cell Values Without Blank Cells


    I have tried your kind suggestion but as soon as one of the values is blank, the font changes from either Red or Green to no format i.e. Black.
    The color change works great as long as there is a value in both cells.

    Re: Conditional Format Cell Color based on Two Other Cell Values Without Blank Cells


    As I understood it, that is what you wanted.


    If one of the cells is blank what colour should Task be?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: Conditional Format Cell Color based on Two Other Cell Values Without Blank Cells


    [INDENT]Hi,
    Using your fomulas, I have just noticed that if I put a dash "-" in the empty cells for example it works properly but I don't want to have to put anything in the empty cells. So it's very close. Is there a way to use the =NOT(ISBLANK(ref)) instead of <>" " ?[/INDENT]

    Re: Conditional Format Cell Color based on Two Other Cell Values Without Blank Cells


    If one of the cells is blank, it should not affect the result. Only the value of the cell in that row.

    Re: Conditional Format Cell Color based on Two Other Cell Values Without Blank Cells


    Try


    Green: =AND(NOT(ISBLANK($A2)),OR(AND($B2>20,$C2>7),AND($B2>20,ISBLANK($C2)),AND(ISBLANK($B2),$C2>7)))


    Red: =AND(NOT(ISBLANK($A2)),AND(OR($B2<=20,OR($C2<=7,ISBLANK($C2))),OR($C2<=7,OR($B2<=20,ISBLANK($B2)))))

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: Conditional Format Cell Color based on Two Other Cell Values Without Blank Cells


    One possibility you did not mention was if there is a Task in column A and both column B and C are blank.


    If you add this as another rule with no format set and put as the first condition with the "Stop if True" box checked then column A will not be coloured.


    =AND(NOT(ISBLANK($A2)),AND(ISBLANK($B2),ISBLANK($C2)))

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: Conditional Format Cell Color based on Two Other Cell Values Without Blank Cells


    Super WOW!!!


    KjBox, Looks like you've done it.


    Many many thanks. Clearly I would never have arrived at those formulas without going mad first. Also, the both blank situation will help too.


    Once again many thanks.

    Re: Conditional Format Cell Color based on Two Other Cell Values Without Blank Cells


    You're welcome.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: Conditional Format Cell Color based on Two Other Cell Values Without Blank Cells


    Hi again,


    Sorry to be a pain once more!


    I've just realised that I didn't allow for any text to be Black. The list gets quite large and it's a little sore on the eyes all in Green.


    Is there a way to have Green fromatting when the values in B2 are between 20 and 100 and the values in C2 betwen 7 and 30?
    This would hopefully leave everything else in Black.


    Also, could you let me know if VBA would be easier for this?


    Thanks!!!

    Re: Conditional Format Cell Color based on Two Other Cell Values Without Blank Cells


    I think this should do what you want for Green, I have not been able to test it so it may not be right!


    =AND(NOT(ISBLANK(A2)),OR(AND($B2>20,$B2<=100,$C2>7,$C2<=30),AND($B2>20,$B2<=100,OR(ISBLANK($C2),AND($C2>7,$C2<=30))),AND($C2>7,$C2<=30,OR(ISBLANK($B2),AND($B2>20,$B2<=100)))))


    You will need to change the Red formula too, to


    =AND(NOT(ISBLANK($A2)),OR(AND($B2<=20,OR($C2<=7,ISBLANK($C2)),AND($C2<=7,OR($B2<=20,ISBLANK($B2))))))


    If this is not right then VBA is probably the way to go, the code can be triggered automatically by changes to cells in columns B and C

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: Conditional Format Cell Color based on Two Other Cell Values Without Blank Cells


    Ok Thanks very much.
    I'll give it a try.
    As ususal, your help is very much appreciated

    Re: Conditional Format Cell Color based on Two Other Cell Values Without Blank Cells


    Hi,
    I am still having problems with Conditional Formatting using the built in Excel formatting which seems to require very long formulas for what I wish to achieve. I was hoping to get help coding with VBA instead.

    I have attached a more exact example of my problem without adding the Excel Conditional Formatting. I hope it uploads with this post.

    The formula in each row of Column "A" refer to all of the posible combinations that I need. They would of course all need to be able to applied to each Cell "G" and "O" of each row and not as they are currently presented if you get my drift.

    I have not been able to include the ISBLANK or NOT(ISBLANK) in all conditions due to lack of skill. I would appreciate any help arriving at a VBA solution. The columns with "TRUE" or "FALSE", only represent the formulas that I would use for the formatting. In reality they would be TASK's i.e. "Replace Oil Filter"


    [ATTACH=CONFIG]65862[/ATTACH]

Participate now!

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

Register Yourself Login