Conditionally Format in VBA

Links to my spreadsheet
Pre
https://drive.google.com/file/d/1fQs...ew?usp=sharing

Final Product
https://drive.google.com/file/d/1Msm...ew?usp=sharing


I've almost fully coded the vba to achieve my desired results but I'm not sure how to conditionally format with VBA.

I want to conditionally format the spreadsheet. Highlighting full rows where sold IS NOT = 0 in green, individual cells where % distant is greater than 50% in yellow, and last disposition is over 1 hour earlier than the max time of the last disposition row also in yellow.

Please help. The code I have so far is below

Please Login or Register  to view this content.

I added the following codes

Removes Grouping, Filtering, then Sort by SOLD {smallest to largest}

Please Login or Register  to view this content.

Color Range("A2:J120") Green IF Range("I2:I) > 0

Please Login or Register  to view this content.

Here's the complete code

Please Login or Register  to view this content.

Originally Posted by dizjackson

last disposition is over 1 hour earlier than the max time of the last disposition row also in yellow.

Requesting additional information on the thinking behind the Last Disposition request.

Thank you so much for your help. The thinking behind the Last Disposition request is that I want to highlight any disposition that is over an hour from the current time but I'm guessing it would be kind of difficult to base my spreadsheet on RealTime, I figured it would be easier to just base it off the latest disposition in that column. Also, every time I run the macro, I'm losing one too many rows from the bottom of the spreadsheet. Could you please inspect the code below for a possible cause. Thanks

Please Login or Register  to view this content.

Originally Posted by dizjackson

every time I run the macro, I'm losing one too many rows from the bottom of the spreadsheet. Could you please inspect the code below for a possible cause.

I've made some minor changes to the code

I replaced the un-merging portion (which took forever under testing mode)

Please Login or Register  to view this content.

I also replaced the third deletion portion of your code which searches & deletes rows based on the word Total

Please Login or Register  to view this content.

Here is the revised code

Please Login or Register  to view this content.

I've added that line but I'm still getting the same error message. Could it be because the name of my workbook is "Time Track (with Macro)" instead of "Time Track". And I've changed the name to Sub KnockReport(). Please assist

Please Login or Register  to view this content.

You haven't answered my question on how you're running the code. see post #8.

The code you just provided (which is similar to your original) does not work properly. It's deleting too many rows, and it doesn't perform the second part of your request.

Please try the code listed below, but do let me know if you run into any issues (as I have not.) thx

Please Login or Register  to view this content.

Edit: Attached is the file I downloaded from the site you provided, and re-saved as a macro-enabled file (code included).

Please download and run the macro within the file I submitted in post 10.

I had three individuals run it on three different OS's (Win 7 pro, 8, 10), and all stated that they did not receive an error.

Confirm when done

I note it works well for me (without error).

Originally Posted by scottiex

I note it works well for me (without error).

Thanks scottiex for the confirmation

Originally Posted by dizjackson

I'm not sure what's going on with mine. I downloaded the exact file from post 10 and running the macro but I'm still receiving the same error. Any settings I need to change with my excel or anything? I feel so close haha

There are no settings that needs to be set. Can you try it on another system? This will help us rule out being an issue with the code.

Thanks

Originally Posted by dizjackson

When I create a new tab to copy and paste a new table and run that macro, that's when I receive the error.

ok that makes since. The code looks at Sheet1 or Sheets("Time_Track")

I'll make changes to the code to reflect the requirements in post #16

deleted....

@dizjackson

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

Sorry for the delay. Someone decided that my house was a five-finger discount store and cleaned me out.

any way...

I've completed your request (from post #16). see code and attached test file

Please Login or Register  to view this content.

Originally Posted by dizjackson

I'm still losing an extra line (name) at the bottom after running the macro.

I manually ran your process, and I came up with the same results.

1. There are 130 records (excluding the header) in the initial file.
2. After running your deletion sequence, there are 67 out of 120 records where column A is blank
3. After deleting rows where column A is blank, there are 53 records left

Which is missing from your list? Here is mine.

• 8 Roger Rabbit
• 8 Buggs Bunny
• 6 Austin Powers
• 9 MC Hammer
• 5 Ric Flair
• 9 Tom Brady
• 8 Michael Jordan

I am not sure why you're getting one less record.
I receive the same results as in post #29 regardless if I run it manually or by the script.
Are you running the code listed in post #27? If not please do so and get back to me.
Thanks

I am not sure what you're doing wrong, or why you’re the only one that’s receiving 52 records instead of 53. Hence, I had 3 individuals, perform the following

1. Download the file listed in your first initial post,
2. Insert and run the macro listed in post #27

They all came back with the same results as I stated in post #29.

I cannot assist further on this thread, unless someone else on this forum comes up with the same results as you.