I am new to Excel macros and am currently right now trying to write a macro that would highlight a row if a condition existed. The best way that I can I explain is it as follows:
If
cell M >= (90% of Cell N) and cell O >= (90% of Cell N)
then
Highlight the row
I have over 3400 rows and I have concluded that writing a macro would be the most efficient way. I am open to any suggestions. Thanks in advance
Last edited by spwolfe; 06-10-2011 at 10:07 AM.
I think this will do, let me know:
Please Login or Register to view this content.
If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.
---Keep on Coding in the Free World---
I really appreciate the response.
I seem to be pulling a "Run-time error '1004': Method 'Range' of object '_Global failed on this line:
Set TtlRng = Range(LRng, HRng)
Hi spwolfe, one thing you need to change and I'm sorry I didn't state it, is the sheets reference here:You need to change that to whatever sheet it is that you want this code to work on. I had it as 6 because that is where I created the mock data in my own workbook.Please Login or Register to view this content.
In fact, you should probably declare the worksheet, which I forgot to do also.
Code Change:Please Login or Register to view this content.
So for example if my sheet's name was Convert, I would do as follows:
Set ws = Worksheets("Convert")
Correct? When I do that I still pull the previous error that I stated.
Perhaps if you can upload a mock workbook with non-personal data, this problem may be rectified.
I really appreciate your time. Here is the link for an example
http://www.megaupload.com/?d=HSUFBXOL
Hopefully that will suffice. Let me know if I should provide anything else.
Can you upload in advanced settings? I cannot access that page because it is blocked by my employer. Or perhaps someone else can get that file and try to fix it!
I apologize about that. I am new to this forum. Maybe this will work.
Hi spwolfe, that was much better for me. I made a slight ammendment to the code.I think the calculation is right but you may want to double or even triple check that the the proper rows are being highlighted. Good luckPlease Login or Register to view this content.
Last edited by Mordred; 06-08-2011 at 03:47 PM.
Ok. I think progress been made with the macro on my part. Now I am getting another error. I am now getting the following:
Run-time error '13':
Type mismatch
on this line:
If cl.Value >= (cl.Offset(0, 1).Value - (cl.Offset(0, 1).Value * 0.1)) And _
cl.Offset(0, 2).Value >= (cl.Offset(0, 1).Value = (cl.Offset(0, 1).Value * 0.1)) Then
I appreciate your patience with this issue.
My apologies, I had a typo. I guess i tapped a key funny. I really really appreciate your help! Thanks a ton!
Sorry to bother again. As I approached the bottom of the worksheet I noticed what you said with the validity. It is not comparing the values of O to the N value.
As you approach the bottom, are there values in Columns M, N, & O that are not numerical?
Attached is the full list of number from M, N, and O
Sorry spwolfe but I have to clarify, in your new upload you have data from Range("M1:037") and then data from range ("A40:C3409"). Should it all be in columns M, N, & O?
This works for me without error (and there are no spelling mistakes this time-sorry)Please Login or Register to view this content.
My apologies about the other spreadsheet. I think it may be just comparing M with N rather than comparing M to N and O to N as well. If that is true then that row I would like to highlight. I tried that last bit of code that you posted and it seems to be pulling the same results. I am going to attach the correct worksheet. Again I really appreciate your time.
Hi spwolf, I wonder if it is a compatability issue. I am at work and I am using Office 2003 while you are using 2010 according to your info. In my version, there are no errors and the code moves fast through the workbook you provided in post #16. I'll ask for more assistance and hopefully there is something minor that can be worked out to get you up and running.
I just ran the same code from post #18 in the new upload from you and received 0 errors so I am at a loss for words right now. Sorry and thanks for YOUR patience on this matter.
It is running without errors, but for instance in column O on the first one it highlights on my spreadsheet, the row should not be highlighted because O is more than 10% of the value of N. I apologize if I haven't made myself clear.
No worries spwolfe, you have been very clear, perhaps my mind is not firing properly today.
spwolfe,
For something like this, no macro necessary, just use conditional formatting.
Attached is a modified version of your sample workbook. The cell range M1:O3409 has had the following conditional formatting rule applied:
=IF(AND($M1>=0.9*$N1,$O1>=0.9*$N1,$M1<>""),TRUE,FALSE)
Hope this helps,
~tigeravatar
You could test which rows should be highlighted with the following formula:
=IF(AND(M1="",N1="",O1=""),"",IF(AND( M1 >=N1*(90%), O1 >=N1*(90%)),"highlight","")) in row 1 and copy down.
The sample workbook and Mordred's code worked for me in Excel 2007 on Windows 7.
Regards
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I just compared highlighted cells from my code to tiger's and they highlight the same so if the results aren't as you want them then you have given us the wrong criteria for what your output should be.
Regards:
Slightly modified version:
select the entire columns A - O and apply at one timePlease Login or Register to view this content.
Ben Van Johnson
Ok guys I think I have figured it out. The formula I actually used after reviewing the last few posts was the following:
=IF(AND($M1>=0.9*$N1,$O1<=1.1*$N1,$M1<>""),TRUE,FALSE)
Attached is the file
I greatly appreciate the help that I have had with this.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks