Hi there.
I have a piece of code that i use to check whether anything has been changed in specific columns using the following code:
What i would like to do is remove the specific ranges and have the code look at every 3rd column starting from Column I. Is this possible?
Please Login or Register to view this content.
Any help is appreciated. Thank you.
Two ideas:
Please Login or Register to view this content.Please Login or Register to view this content.
Last edited by JBeaucaire; 07-29-2013 at 03:15 PM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Hi JBeaucaire,
Sorry i wasn't very clear in my original post.
The reason I am asking to get away from having the specific ranges is because later on down the road more items can be added to the sheet further increasing the ranges the code has to look at. If someone else begins using this workbook and isn't very familiar with VBA, i don't want them to have to go into the code to make any changes. So what i am looking for is a piece of code that will work for every third column in the entire worksheet.
any idea?
Last edited by JBeaucaire; 07-29-2013 at 03:15 PM. Reason: Removed unnecessary QUOTE. Just use the REPLY box below.
Use the second one, then, take out the middle AND section highlighted in red.
Awesome. Thank you so much!
Hi Again,
I had a similar issue I am looking to address that is part of the same code.
Wondering if there is anyway to apply the above code JBeaucaire provided with the last portion of my code. I am looking to have that code apply to every third column again.Please Login or Register to view this content.
Without thinking or talking in code, describe breifly what you're trying to accomplish.
What that last code does and what i want to accomplish is the following:
I have a "Detail" sheet that has columns for locations. Each Location column has 3 sub-columns underneath it with different information. I want to look at one of those specific 3 sub-columns (the ones labeled in above code). Once the specific sub-column for each location says "Complete" i want the first 6 cells on that active row to turn green to indicate the entire entry is complete. Even if one of them says "In Process" or "Planned", etc, it should remain unchanged.
Let me know if you need me to go into more detail.
Last edited by Spritz; 07-30-2013 at 04:28 PM.
You should resist using VBA to accomplish what basic builtin functions can do in Excel. You can apply a Conditional Formatting rule to the first 6 columns to watch that "completed" column and change to the needed color when the value in that row is "Complete".
Hmmm... I can't think of how i would apply that conditional formatting. The thing is, this workbook will be updated constantly and new locations will keep being added. If i have 25 locations that means the conditional formatting has to look at 25 Columns for "Complete". Even if one of those locations are still "In Process" I do not wish the first 6 columns to change color. ONLY when ALL 25 columns say "Complete". However, these columns are not side by side, they are every 3rd column. The first column is whether or not the item is applicable to the location (Y/N answer) and the second column has information while the third is the one where I need to look for "Complete". So if i have 25 locations that means there are 75 total columns + the original 6.
Couldn't you go the other way, and use CF to watch all the cells to the right of the first 6 columns and NOT color them if it sees "In Process" anywhere on that row?
1) Highlight A2:F2
2) Apply a CF rule like:
=COUNTIF($G2:$IV2, "In Process")=0
Perhaps a better one to make sure there is also at least one "Complete" would be:
=AND(COUNTIF($G2:$IV2, "In Process")=0, COUNTIF($G2:$IV2, "Complete")>0)
Good call. That worked but to apply it to every item would probably take a lot longer than just adding extra columns in the VBA code. I would also have to accommodate for future additions and apply that CF rule to extra rows. There will be far less location additions (Columns) than item additions (Rows). Thanks for all your help JBeaucaire.
After you have entered the CF to row 2:
1) Highlight A2:F2
2) Copy
3) Highlight A3:A10000
4) Do a Paste Special > Formats
Now its working for all those rows and will do so as you add data to your table.
The original CF formula i suggested is already designed to look out to the right for 250 columns, that's enough for 80+ "groups" of data, as far as Excel 2003 will allow.
Nice. Had to make a few changes to match what my actual results would be but worked perfectly.
Final solutions:
Green colored cells (Complete):
=AND(COUNTIF($G16:$CZ16, "Planned")=0,COUNTIF($G16:$CZ16, "In Process")=0, COUNTIF($G16:$CZ16, "Complete")>=1)
Yellow colored cells (In Process):
=OR(COUNTIF($G16:$CZ16, "Planned")>=1,COUNTIF($G16:$CZ16, "In Process")>=1)
Blank Cells:
=AND(COUNTIF($G16:$CZ16, "Planned")=0,COUNTIF($G16:$CZ16, "In Process")=0, COUNTIF($G16:$CZ16, "Complete")=0)
Thanks again! Much appreciated.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks