Conditional Formatting behavior question

[Mr_Phil]

I know that conditional formatting can be a problem. But, can you minimize it?

Example: I currently highlight the entire row of data (85 columns) when the persons status changes to TERMINATED or OUT TILL FURTHER NOTICE or LEAVE OF ABSENSE or TEMP WORKING OUT OF AREA etc.

In the past I think I had some workbook issues due to that. If I reduce the application so that the rule is applied only to a cell vice the row would that help do you think? Or instead of coloring the entire row maybe just coloring the font in that row?

There's a lot of people to track and CF makes it easy at a glance. But if I can use it with less risk of errors or issues I'd love to.
What do y'all think? And, as always I am extremely grateful for the time everyone takes to read and reply. Thank you.

 

[Joe4]

I am not sure I understand what "kind of problem" you are talking about.
Is it not working the way you want (if you, then you probably just have not set up the rule correctly)?
Or is there some sort of performance issue?

If it is a performance issue, how many rows of data do you have?
Are you formatting just those rows, or the entire sheet?
Are you formatting just those 85 columns or all columns?

 

[Mr_Phil]

From time to time I would have performance or stability issues. Excel (that workbook only) would lock up / freeze. I would open and repair and it always stripped out some of the conditional formats and always from the person tab.

On the rows I only highlight the first 85 columns
I have about 200 rows on that tab.

I have other tabs (resource, equipment, parts to persons, hours aggregates from time cards, etc) that link with the persons tab but they don’t have any conditional formats. Not more than 20K rows ranging from six to ten columns total.

I have one other tab that ties all teh above together to make a warehouse pick sheet that is heavily conditionally formatted and relies on a lot of xlookup and macros. I am slowly converting my VBA to power query as I think it will get rid of some bloat and might speed up a bit.

Thank you for looking and engaging.

 

[Joe4]

If I can be frank, you are probably using the wrong tool for the job. What you are describing sounds like a Relational Database (lots of inter-related data).
While you can often "brute force" Excel to behave like a Relational Database, it is not what it was designed to do, and it is often cumbersome and poor performing (as you can see).

It would be much better to use a program designed for Relational Databases, like Microsoft Access, SQL, MySQL, or Oracle.

 

[Mr_Phil]

If I can be frank, you are probably using the wrong tool for the job. What you are describing sounds like a Relational Database (lots of inter-related data).
While you can often "brute force" Excel to behave like a Relational Database, it is not what it was designed to do, and it is often cumbersome and poor performing (as you can see).

It would be much better to use a program designed for Relational Databases, like Microsoft Access, SQL, MySQL, or Oracle.

I like Frank. Some say I am too Frank myself. I understand what you are saying and agree with you. When I first took over the warehouse it was all manual on MS Word documents. I retired from the Marines 7 years before that and was actually proficient in then current version of Access and of course the predecessors like Lotus and dBase. So, I automated everything and then found out that the company is phobically excel centric. I like learning new things fortunately. I tried to show others that Access was better for big data management and they were not interested. So I could keep my Access .mdb but there was no way to collaborate and exports from the cloud based systems were all in excel. So, I had a leg up knowing Access and basic SQL for learning excel. I like the program and would cheerfully demonstrate what I can make happen after studying the issue here, and online elsewhere just so you could be impressed at the number of workarounds and kludges that work. But instead of a trim application it is big & slower than I like. As long as the paychecks still cash I will keep on working within the box. I was just hoping someone knew a tweak I hadn't seen before.

Thank you very much for your time spent. I do appreciate it.

 

[Joe4]

Understood. I know of any companies that get stuck in that line of thinking.
I would encourage to continue on with your thoughts of moving a lot of it to Power Query, if possible.
I have not used Power Query much myself, but understand that it allows you to perform database-type operations against Excel much more gracefully.

 

[Mr_Phil]

Understood. I know of any companies that get stuck in that line of thinking.
I would encourage to continue on with your thoughts of moving a lot of it to Power Query, if possible.
I have not used Power Query much myself, but understand that it allows you to perform database-type operations against Excel much more gracefully.

Yessir. PQ for sure is less of a space hog. I know we live in a terrabyte world but I grew up in kilobyte county. As I redo the VBA into PQ I have shed about 21mb. The tables all look the same other than formatting but PQ somehow slimmed it down quick. Replaced several hundred lines of code. According to my counter macro I still have this many to work thru to PQ Paradise. Thank you for the help. And if you are inclined, Merry Christmas. If not, Best Wishes and Peace.

Thank you for the help. And if you are inclined, Merry Christmas. If not, Best Wishes and Peace.