what do I need to hightlight the yellow rows automatic.If no date in columns I or J then row is yellow.If date in either I or J then no formatting.
[ATTACH=CONFIG]63803[/ATTACH]
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. -
Re: Conditional formatting
Thank You NBVC, work perfect.
-
Re: Conditional formatting
If A1 to H is empty then no format, would this need a second rule?
-
Re: Conditional formatting
Perhaps revise the previous condition formula with:
[COLOR="#0000FF"]=OR(COUNTA($A1:$H1)=0,SUM($I1:$J1)=0)[/COLOR]
-
-
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]
-
Re: Conditional formatting
Thanks for hanging in there, here is a pic of what happens when formula is copied down.When there is no data in a row can it be normal formatting.So
conditional formatting only applied to row with data. -
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.
-
Re: Conditional formatting
[ATTACH=CONFIG]63807[/ATTACH]Getting error
-
-
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.
-
Re: Conditional formatting
Thank You will give that a try,not sure if you can do that with a command button.
-
Re: Conditional formatting
Record a macro and apply it to the button.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!