Conditional formatting

  • Re: Conditional formatting


    Select from A1 to last cell in column H.
    Go to Home | Conditional Formatting | New Rule
    Select "Use a formula to determine which cells to format"
    Enter formula: =SUM($I1:$J1)=0
    Click Format and choose from Fill tab.
    Click Ok.
    Click Ok to finish.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Conditional formatting


    Perhaps revise the previous condition formula with:


    [COLOR="#0000FF"]=OR(COUNTA($A1:$H1)=0,SUM($I1:$J1)=0)[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Conditional formatting


    Thanks NBVC
    Sorry I do not explain results I want very good,I want the entire sheet to stay white with black font.When I copy formula down it fills the whole sheet,so if there is no
    data in A10 and beyond and I fill the formula down to A100 it turn the area colored.Can the formating for a-H only work when data is in row A

  • Re: Conditional formatting


    I am a bit confused. Do you mean you have formulas in the cells that might return blanks?


    Maybe my formula should be adjusted to:


    [COLOR="#0000FF"]=OR(COUNTIF($A1:$H1,"?*")=0,SUM($I1:$J1)=0)[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Conditional formatting


    Ok, I think I understand...


    Try:


    [COLOR="#0000FF"]=AND($A1<>"",SUM($I1:$J1)=0))[/COLOR]


    This will only highlight the row, if there is something in column A and no numbers/dates in I and J.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Conditional formatting


    Got it, now that is what I call Perfect. Thanks NBVC
    Should I start another thread for different question on this,is it possible to print the colored rows only?

  • Re: Conditional formatting


    If it goes further than this, then I'd say better to start a new thread.


    for now try, Go to Data|Filter, then you can filter by colour. Then print.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Conditional formatting


    Record a macro and apply it to the button.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

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