Tired Conditional Format Needs VBA Upgrade expertise desperately to survive.

[dbsbender]

Oh I wish I was smarter. I was tasked with making my workbook actually work - and be smaller so I could email it out. I think VBA may be my only answer. Another genius here helped me come up with a conditional format for my worksheet. Here is the scenario. You can also look at the attached.

I have a table with columns from A - Z. In column A3 is Date In. B3 is Date Out. C3 is =networkdays(). Normal data, (text, dates, general and accounting take up the rest of the columns).

The Condition: When a value is entered anywhere on a line (other than B3) the whole line will turn our favorite bright yellow). When a date is entered into B3, the whole line is unhighlighted, regardless of what else is entered. Keep in mind the formula for network days may be a challenge?

While I'm asking for the moon, the sheet name should appear in A2.

This would need to be in up to 5 worksheets, but could be less.

Is anyone up to the challenge of making my day any better? In advance, thank you so very much. I am very grateful.

[FDibbins]

It would probably help more if you added some sample data, and showed what you expect?

[FDibbins]

This would your CF rule...
=AND($B4="",COUNTA($E4:$Q4)>0)

[FDibbins]

This will pull your sheet name for you...
A2=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

[scottiex]

I guess

Please Login or Register  to view this content.

But I don't see how this is helping to make it faster/smaller/more email-able etc...

Was your conditional formatting causing it to crash somehow?

[dbsbender]

I have added some data. Thank you.

[dbsbender]

I'm not sure what made it crash. It would just spin and spin and the only way out was to hard power down. Opening in safe mode doesn't make my IT group very happy.

[dbsbender]

Thank you for this one.

[scottiex]

I think there can be an issue with lots of conditional formats and many filters on a worksheet. Maybe that was happening.

But I'm wondering if there is another issue at play here that we are not actually fixing - although it is very hard to figure that out remotely.
was your file size getting huge?
did you have millions of rows (ie the scroll bar is tiny?)
do the users have any addins?

Maybe someone else has ideas.

[FDibbins]

Thank you for this one.

Does that mean my suggestions worked for you?

[dbsbender]

Thank you all. I will give these a try today and see if it works. If not, I may try to bring in the original file. Thanks for being willing to jump in and help out.