I can only echo the sentiments of many others who have watched and learned from this video. Your presentation skills are exceptional. Your step-by-step instruction was terrific. I struggled a little with the syntax as I am not familiar with VBA at all. I persevered and voila! Success. Thank you.
That was amazing! I wasn't even intimated by the VBA, complete stranger! Loved the way it was presented! Everything was clear and concise. You are a wonderful teacher! Thank you!!!!!
Hello Set, thanks so much for your comment. That is a nice idea and I have added it to my list. Thank you for your Likes, Shares & Comments. It really helps.
Hi thanks so much. You would want to convert the table to a range so that the conditional formatting takes precedence. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Wow! I did my first vba today and it works perfectly!!! Thanks so much for your videos, I really appreciate them, they are improving signifficantly my excel knowledge.
HI and thanks yes it is possible. I show you how to do that in this video: ua-cam.com/video/6Qgy1XVJYhY/v-deo.html Thank you for your Likes, Shares & Comments. It really helps.
Hi Randy.. great tip.. I just came across this video. Here is a method that I have used in the past. Do the same Conditional Formatting as in your video, but using the formula: =CELL("row") = ROW(). Then, the command for the Worksheet_SelectionChange event becomes: ActiveSheet.Calculate. This avoids the need of referencing a cell for the row reference from the face of the worksheet. It is just another way to get it done. I do like your method, as I think it produces less calculation overhead which might slow performance on a complicated workbook. I use my method on simple lists where performance is not an issue. For complicated workbooks, I'll definitely give your method a try in the future. Thanks for expanding my knowledge. Thumbs up!
Hi Wayne, Thanks so much for the formula, That makes perfect sense. I can try that one as well in some of my workbooks. Thanks for following and I look forward to creating many more applications right here.
THAT'S GREAT. HELLO, DO YOU KNOW HOW TO USE CONDITIONAL FORMATING TO HIGHLIGH ROWS AND COLUMNS WHEN MULTIPLE CELLS ARE SELECTED. SUCH AS WHEN SELECTED FROM A3 TO D5 WITHIN A RANGE THE COLUMN FROM A TO D AND ROW FROM 3 TO 5 ARE HIGHLIGHTED. THIS NEEDS TO BE DYNAMIC TOO. THANKS IN ADVANCE.
HI and thanks Anthony. All code, formulas and conditional formats only need to be set once and saved. It will be available each time you open your workbook. I hope this helps and thanks so much.
@@ExcelForFreelancers I'm considering subscribing to the Monthly training program. However, I have no good background of excel (codes and formulae). I am highly motivated to learn Excel applications. I have been trying to learn common functions, I feel I can do something. I thought I could develop some applications to help schools, health centres, small businesses. I see the market is wide open and very few people are concerned about these opportunities. Kindly help
Hi Anthony, thanks so much. It is a great opportunity and great course to teach you how to create and sell your own Excel-based applications. If you are unsure of your VBA skills I have a free VBA course taught by MVP Sumit Bansal on my site right here: www.excelforfreelancers.com/enroll-in-the-free-excel-vba-course-by-mvp-sumit-bansal-of-trumpexcel/
No importa a lo que te dediques hay dos cosas que debes aprender si o si: Ingles u Excel. No matter what your job is, there is two things you've to learn: Excel and English. This video was made a long time ago, and it still being useful. That's. I just meet you, and I've a lot of video to see in your channel. From Nicaragua (Central America) regard
How would you make the last row dynamic so the format grows as the table expands without having to go in and change the range everytime? Thanks for help
HI thanks you can make the last row dynamic using a variable such as LastRow = Sheet1.Range("B9999").end (xlup).row I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
You could change the range in your code to a large number: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("E5:K999")) Is Nothing Then Range("A4").Value = Target.Row End If End Sub With the conditional formatting: =AND($E5"",$A$4=ROW()) the selected row isn't highlighted unless there's a value in column E. HTH.
So I’ve been trying to find a way to apply this to many sheets and existing excel workbooks. Would I have to do this process for all the exiting workbooks and worksheets??
If all of the sheets are similar you can use "ActiveSheet" instead of the specific sheet. That should work fine however you will want to make sure that the cell used for the selected row is in the same cell for every sheet. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Hi, Undo can also be incorporated as well. In the File Manager, Part 3 I use Application.Undo, in case the user makes an incorrect Drag and Drop. You can check it out here: ua-cam.com/video/gZc4n0SdmHI/v-deo.html
Thank you so much, this is another great video. Might I suggest you show us another approach where we have a narrow column on the left where we can toggle the row highlighting on or off? This gives us the freedom to have several rows highlighted.
Hi Tony, thanks so much for your suggestion and comment. That makes a lot of sense. I do have this video which shows how to highlight multiple rows in a table: ua-cam.com/video/Ltx7DLLGGHo/v-deo.html
Hi Randy, is it possible to make the highlighted Row dynamic. Like when the data rows with value are increasing, the target range is also increasing..?
Hi and yes you can use dynamic named ranges using the offset formula and for conditional formatting you can include a large range for the max rows. I hope this helps and thanks so much.
Do you have a video that shows how to highlight the row and column labels of the active cell? In your video, the active cell row and column labels are highlighted yellow. Do you have a video and/or tutorial on how to do that?
Hi and thanks, I only have active row, however you can use another cell to display the selected column such as Range("A1").value = Target.Column then write a new conditional formatting for the "Column()" instead of the Row() I hope this helps and thanks so much.
Great tutorial Randy. I have a spreadsheet with 5000 rows and this works well until I have to insert a row at the top. Then it does not include the inserted row unless I go back and change the range. Is there a way to make it dynamic? Thanks
With VBA I would just insert the row at the bottom and then resort your list based on something like an 'Added On Date' so this way the newest is always at the top but without having to actually insert any row, which can mess up both conditional formatting and VBA. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Hi, thanks so much for sharing. It works great in my case except when the worksheet I'm working on is locked. I wonder if there's a way to work around it.
Having a locked sheet is ok, but you have to make sure any cells which changes are being made to (like the selected row #) are unlocked. This is important that these specific cells remain unlocked so VBA can make changes to the cell. WHen a sheet is protected vba cannot make changes to a protected cell I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
@@ExcelForFreelancers Hi.. see my method above using =CELL("row") = ROW() for the conditional formatting formula. This works even if the sheet is protected, as the Worksheet_SelectionChange event does not write back to the worksheet.. so no conflict. It only calculates the worksheet via ActiveSheet.Calculate to update the results of the formula driving the conditional formatting. Give it a try.. hope it helps you. Thumbs up!
This is an interesting use of conditional formatting; it only works when the sheet is unprotected how can I get it to work when I protect sheet. Please send link on this topic if there are any Thanks, keep these tutorials going
HI thanks this will work regardless if the sheet is protected or not as long as you unlock the cell in which the row is being applied or use xlUserInterfaceOnly within VBA (Which allows VBA to make sheet changes but not the user) I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Thanks for the informative presentation. Instead of defining the range of cells in the table, can you define the table as an Excel Table, give it a name, and them use the table name inside both VBA and he Conditional Formatting?
Hi Jack that is correct however I have found using tables to be somewhat restrictive for my purposes. Since we do a lot with data and in my larger applications, data is complete separate than the user interface, I generally don't use tables. Thanks for the comment and input.
Gr8 sir, I am searching for this whole day, and I found excet what I want, thank you so much, one little question can we save this as a micro so we can use in which file we want Thanks again sir
Hi Samir, thanks so much. I am glad you enjoyed the training. You can export or save this module or you can copy and paste it into any other workbook. I hope this helps and thanks so much.
Hello Randy and thank you for the video - it is very helpful and does the trick. There are 2 questions though - 1.as soon as I applied all these steps to my worksheet - the active cell’s row is highlighted all right, but Do and Undo functions stopped working and disabled- to be exact they work only if I edit the cell but don’t hit Enter yet. As soon as I hit the Enter - no more Do and Undo functions. I figured out it is VBA code doing - as soon as I have it disabled - Do undo are restored. 2. The code works only for mouse double click. It seems to do nothing for first\single click. Do I do something wrong??? Does it happen to me only or to anybody else as well?? I use W7-64 and MS-Office 2016. Thank you
Hi and thanks, when VBA runs, the clipboard is cleared, preventing the Undo option. The Selection Change event will work for any event when you make a selection change You may want to consider enrolling in Daniel Strong’s Ultimate Excel VBA 30-hour course, in which I have secured a special discount for Excel For Freelancer Followers right here: bit.ly/VBACourse_Discount
ThnX Randy, I have implemented this great trick into my members sheet but I run into a problem. If I do some format changes I can undo those actions before VBA row selection, after VBA the button UNDO is greyed out and can't undo any format change...... ThnX in advance
Hi Jose, VBA does not allow us to Undo actions after VBA has run, however I have created two-part video on a special way on how to get around this right here: Part 1: ua-cam.com/video/6TnxPvLz_44/v-deo.html Part 2: ua-cam.com/video/fE15W72vnEE/v-deo.html I hope this helps and thanks
Thanks for the well paced and simple explanation. I've noticed that after implementing this code that I am unable to undo (CTRL+Z). I'm assuming this is because the last action was the VB script? Is there a way around this?
Hi and thanks, yes this is cause from VBA when its run the history is cleared, You can also try this video to undo changes ua-cam.com/video/6TnxPvLz_44/v-deo.html
Hey this is awesome! I was wondering if there is a way to do this without a helper cell? I'm stumbled upon the code, but the logic (i think) would be: 1) Clear formatting of the previously selected row, 2) Color code the newly selected row. What i can't seem to get around is step 1, how would i capture the previously selected row?
Hi Miaden, thanks very much. I am so glad you like it. There is a way to simply color the selected cell, Row, or Column, using VBA without a helper cell and without conditional formatting, however you would either, have to clear the formatting of all cells in a sheet or use a cell to 'remember' the selected cell in a sheet. So that when you select a cell, you can use a hidden cell to remember such as Range("B1").value = Target.Address, Then when selecting a new cell, you can clear the formatting using the saved cell address.With that said it may not be any easier so the Conditional Formatting option may be quicker and easier. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Great tip and very useful. However, I have noticed that when I click away from the highlighted row and into an area on the worksheet that the macro does not cover, the highlighting does not clear. Am wondering if I made a mistake in the VBA code or not.
Hi and thanks for your comment, You can simply add a 'ClearContents' line for the specific range to clear the contents using an else statement, something like if Not Intersect(Target,Range("A10")) is nothing then Range("A1").value = Target.Row Else Range("A1").clearcontents End If I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Just like you add the selected Row # in one cell, you would add the selected column # in another cell. Say that the row # is in B1 and the Column # is in B2, then just change your conditional formatting to this rule: =AND($B$1=ROW(),$B$2=COLUMN())
Hi, can you show tutorial on how to do the listbox in user form become grid box?and only display selected row header in excel. Really appreciate your help
Hi and thanks I will try to add this in the near future however you can request this in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 30,000 Members who would love to help you with this. Thanks so much.
THAT'S GREAT. HELLO, DO YOU KNOW HOW TO USE CONDITIONAL FORMATING TO HIGHLIGH ROWS AND COLUMNS WHEN MULTIPLE CELLS ARE SELECTED. SUCH AS WHEN SELECTED FROM A3 TO D5 WITHIN A RANGE THE COLUMN FROM A TO D AND ROW FROM 3 TO 5 ARE HIGHLIGHTED. THIS NEEDS TO BE DYNAMIC TOO. THANKS IN ADVANCE.
Hi and thanks so much. I am glad you enjoy the content. For selecting multiple rows, you can check out this training video: ua-cam.com/video/Ltx7DLLGGHo/v-deo.html I hope this helps and thanks so much.
Hi Roy thanks for your comment. Yes it can, as long as you allow users to select protected cells and make sure that the cell in which the row # is changes is not protected or use xlUserInterfaceOnly within VBA when setting up the protection which allows VBA to make changes to protected cells. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
can you guide us where to learn how to format a cell based on TIME (like before 12 pm format specific color, and between 9 am to 12 pm different, betwwen 11: pm and 4 am different color) will be much appreciated
HI and thanks very much. Time is simple a number in excel so you can use the < or > to within a formula to determine if it is before or after a specific time. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Just wondering if you would be interested in doing an update on this video to highlight the column as well as the row. I have a speadsheet to keep track of all my Lego parts which is too large to fit onto one screen, and it would help when scrolling across the sheet.
Hi and thanks so much. You can add an additional conditional formatting rule in which you put the selected column in a cell, such as A1 and then you the conditional formatting rule would be =$A$1=Column() I hope this helps and thanks so much.
Yes for sure, in VBA you would choose a different Cell and add that column such as Range("A1").value = Target.Column (this will set the column # into the cell, then Conditional formatting will take care of the coloring of the column) I hope this helps and thanks so much.
Best For ever...................... One Query : - how to highlight particuler cell via drop downlist " Condition :- 50 Document names in my list , highlight list via Country name & Party name , using Dropdown list.........
Hi thanks you would just have to set up a Conditional formatting based on the selected values within that drop down list and matches within a range. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Randy, what you do is simply amazing. Just started to follow your on-line trainings and wanna keep it on like that. Having seen (and then trying to put it to work on my own) your 'highlight selected row' tutorial I came across a question which is: if you have not just one, but two separate tables on a given worksheet, how do you code VBA to make the rows be highlighted on BOTH tables independantly? I am a pre-beginner VBA dude, and a "VBA for Dummies" won't come handy on the topic... Tried to duplicate the code as you tought in your video, + changed the target range so the two tables would be distinguished. All I got in return was an error mssg saying "Ambiguous name detected: Worksheet_SelectionChange"... Tried to use some common sense, but failed... Can you (or anyone of your followers) help somehow, please? I am not a FB user, so this is my only chance to look for some support. Many thanks. Greetings from the other part of globe. Chris
Hi and thanks for your comment. I am glad you like the training. You would want to use two different cells to keep track of the row and two different selection change ranges when highlighting rows in two different tables, where B1 would set the highlighted row for one table and B2 would set the highlighted row for another table. Perhaps something like this Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("C5:M100")) Is Nothing Then Range("B1").Value = Target.Row End If If Not Intersect(Target, Range("P5:S100")) Is Nothing Then Range("B2").Value = Target.Row End If End Sub I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Sooper excellent sir Sir please make a video on simple excel school management software.... Basic need is school fee collection and balance of each student... And bus fee collection and balance (bus fee change depends on area) Thanking you sir
Hi thanks I might do this in a course, but it would not be for at least a year since I have a list of courses to create already. Thanks for your patience. You may want to have one of our amazing developers create this for you as a Paid Job by posting in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel
Hi that's an excellent question. When i started doing Excel Freelancing about 10 years ago I knew only a few Excel skills and almost no VBA. The basic principal for almost any Freelancing job is that you have to solve a problem for a customer and do it in a manner of value (money) for both you and the customer. Excel is a great to get that done, however there is a great deal of more than just Excel to learn (Sales, Customers, Marketing, etc) A great way to start is on Upwork or FIver where you can offer your services. Be specific with the work that you can do and do not promise work that you are not capable of (yet). As you take on more jobs, your skills will grow. Excel is a Massive application so no one person knows everything about Excel. Some are FOrmula Experts, Others are Pivot Tables, and some love data. My specialty is taking Excel and creating actual applications from it, then selling those applications (Like this: www.labor-burden-calculator.com ) I will be offering courses (small and large) So please join our Excel for Freelancers Facebook group here: bit.ly/groupexcel and also make sure you sign up to get all the free downloads from my facebook.com/ExcelForFreelancers/ page. Thanks so much, Randy
I tried before another way to achieve this. But I struggled with not being able to copy and paste any longer because of this. So I'm wondering if your way has the same problem of the function of copy and paste being not working any longer .
HI Mufrad, that was just a random row within the table to set the initial conditional formatting, however when a user selects a row in a table, it will change. Thank you for your Likes, Shares & Comments. It really helps.
I want to highlight those rows which are having same values in cells in perticuller column. Example column d is having post codes.So I want to hightlight active rows when same post code is there in D column
Hi thats a great question. There are a few good ways to do that. I have a group of 5,000+ Excel Experts on Facebook, Can you please post your question, along with your screen shot or workbook, there so we can help you get the answer you need right away? bit.ly/groupexcel
Hi thanks when you protect a worksheet you can set the 'select locked cells' which will allow you to make a selection of locked cells, Just make sure that the cell in which vba places the row # is unlocked. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Thanks Randy. This works. I will be connected with you & will definitely post you if any additional help require. Once again thanks for your quick response. Your tutorial is very helpful.
thank you so much sir for your video but I receive an error said that the argument is not optional if you let me know how to fix this problem I would really appreciate it thank you again
Hi thanks it would be difficult to know the issue without seeing the code and the workbook. Can you upload it to our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 25,000 Members who would love to help you with this. Thanks so much.
Hi thank you will need to remove the protection on any cells that are changed by the user or VBA. Thanks very much. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
HI Robert, Thanks for your comment. I have a video training and download that shows you how to create your own log history with Undo (since VBA automatically clears the clipboard when a macro is run). Here is the two part training ua-cam.com/video/6TnxPvLz_44/v-deo.html
Hi and thanks you can use ActivSheet on any macro and also copy and paste the selection code in each sheet you want to have the feature. I hope this helps and thanks so much.
Hi please try "Sheet2.Range("B4").value = Target.Row I have a free VBA course taught by MVP Sumit Bansal on my site right here: www.excelforfreelancers.com/enroll-in-the-free-excel-vba-course-by-mvp-sumit-bansal-of-trumpexcel/
Not with this type of selection change because the macro places the selected row in a cell which is used for conditional formatting. Thank you for your Likes, Shares & Comments. It really helps.
HI and yes, inside the VBA Worksheet_Change event you can write Application.EnableEvents = False Application.Undo Application.EnableEvents = True This must be the first lines of code, right after the Worksheet_Change event line and before any other code. I hope this helps and thanks so much.
I have used this coce in your One click view file but it was showing Invalid Outside Procedure. I have copied this code in sheet(Setup) just above Private Sub worksheet_change(byval target as range). Let me know if I have to paste it somewhere else.
HI using Selection Change or running the code, generally clears out the clipboard however if there are specific copy/paste options you would like to perform you can probably do it through a macro. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Sir, I have pivot table where is text as value with subtotal. How can I auto hide row for subtotal on items that i don't want to show in pivot table when select change slicer
Hi , Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 50,000 Members who would love to help you with this. Thanks so much.
If I filter the data in the table then remove filter highlight works only in the first cell on selected row and other cells in the same row not highlighted , please advise
The highlighted row, is based on a Row Number, therefore if you filter the data, the rows may be hidden or reorganized. Also make sure that the Row Number is not cleared on filter. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question with code screenshots here:bit.ly/groupexcel
Hi and thanks can you post your question, with screenshots, in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 40,000 Members who would love to help you with this. Thanks so much.
I don't have Excel access until tomorrow, but could you do without the A$ cell being updated by the macro and just refer to target.row in the conditional format rule?
Hi Scott, the conditional format is based on a specific row. That row has to be placed somewhere so the conditional formatting rules will recognize which row to highlight. I hope this helps and thanks.
What should we do for using in multiple worksheet on the same workbook? Also, after saving the file, close and open, the VBA stops working and just leaves a row "conditionally formatted"?
Hi Matthew, thanks for your comment. You can copy and past the code into any sheets. Also please make sure to save the file as an xlsm or xlsb type so that macros are saved as well. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
@@ExcelForFreelancers Thank you! I have been playing with this and VBA. I made two workbooks and tried to add this to "simple trick" to another file and I watched this three more times before I asked myself. "I wonder if he replied to my comment?" LOL Thank you! I just applied to the FB Group! [I was using the wrong file type].
One thing I spotted while using this vba. When the user is pressing select all in the upper left area, a bug appears. In order to solve that, after the if condition, I entered: If Target.Rows.Count > 1 Then Exit Sub Probably the case was already solved on facebook group or something. Just in case for those that don't know it yet :)
Hi Dragos, yes you are correct. In most of my workbooks I now use If Target.Rows.CountLarge > 1. This also fixes the problem. Thanks for catching this.
Hi Thanks for your comment and question. You have a few options to change the protection or unprotect & reprotect within the code For additional questions on Excel or VBA, please join our Facebook Group, as well have 7000 Excel experts who can help you here: bit.ly/groupexcel
![Create This AMAZING Excel Application that Tracks Purchases, Sales AND Inventory [Part 1]](http://i.ytimg.com/vi/-1N0L-FDWCs/mqdefault.jpg)
📅Excel Calendar Pro: Your Solution to Stress-Free Scheduling 👉 rebrand.ly/CalendarPro_PinnedComm
I love how you teach excel...I wish I had you for a teacher..I would have aced all my classes. You are awesome and you rock.
Thanks so much Iloco, I do appreciate that.
I can only echo the sentiments of many others who have watched and learned from this video. Your presentation skills are exceptional. Your step-by-step instruction was terrific. I struggled a little with the syntax as I am not familiar with VBA at all. I persevered and voila! Success. Thank you.
Wow, thank you! I am so glad you enjoyed the video, and keep learning. You are doing great Robert.
That was amazing! I wasn't even intimated by the VBA, complete stranger! Loved the way it was presented! Everything was clear and concise. You are a wonderful teacher! Thank you!!!!!
For sure, you are very welcome and thanks so much.
Thank you Randy, sweet and short and to the point.
For sure, you are very welcome Kwena
This is an amazing technique, I wanted to learn since a long time. You presented it in a clear and excellent way. Thanks indeed.
I am so glad this training helped you. Thanks a lot.
You are the best teacher out there, and thanks for the videos they very helpful and please keep us updated again Thanks
Thank you so much Seichy, I am so glad you like the training.
Honestly I think you're the best
Wow, thanks so much. Very kind of you.
Hi Randy. Thanks for this awesome training. I would wonder if you could make a short training on how to print all records at once. Thanks again.
Hello Set, thanks so much for your comment. That is a nice idea and I have added it to my list. Thank you for your Likes, Shares & Comments. It really helps.
Thank you for this. its really a big help. I am actually using this trick right now. and my database did stood out. Big thanks.
For sure, you are very welcome I am really glad i was able to help. Thank you for your Likes, Shares & Comments. It really helps.
Thank you so much. I absolutely love this technique
Great, thanks so much. I am so glad to help and share
Best ever use of conditional formating to higlight the row
Thanks very much. I have seen so many people do it different ways, however this just seemed the quickest and easiest
This is excellent. Thank you. Why did you enter the number 9 and how would you adapt the code if the range was an Excel table. Thank you.
Hi thanks so much. You would want to convert the table to a range so that the conditional formatting takes precedence. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
@@ExcelForFreelancers Thank you.
Wow! I did my first vba today and it works perfectly!!! Thanks so much for your videos, I really appreciate them, they are improving signifficantly my excel knowledge.
I am so happy to hear that Carmela, It's great that you are trying and learning new things. I am glad i can play a part in that.
This was a very good video. Simple way to highlight the row or column. Great work and good explanation.
Thanks so much Mohan. I am really glad you liked it.
This is my second comment after I tried this on my own spreadsheet I am so grateful.. You are a star 🌟. 👍 Thumbs up..
Great to hear that K D. Keep up the great work and learning.
Respected Sir ! you are really one of the greatest teachers.
Thank you so much Aziz, I really do appreciate that.
Is that possible to apply conditional formula for example highlight selected row by vba code
HI and thanks yes it is possible. I show you how to do that in this video: ua-cam.com/video/6Qgy1XVJYhY/v-deo.html
Thank you for your Likes, Shares & Comments. It really helps.
Hi Randy.. great tip.. I just came across this video. Here is a method that I have used in the past. Do the same Conditional Formatting as in your video, but using the formula: =CELL("row") = ROW(). Then, the command for the Worksheet_SelectionChange event becomes: ActiveSheet.Calculate. This avoids the need of referencing a cell for the row reference from the face of the worksheet. It is just another way to get it done. I do like your method, as I think it produces less calculation overhead which might slow performance on a complicated workbook. I use my method on simple lists where performance is not an issue. For complicated workbooks, I'll definitely give your method a try in the future. Thanks for expanding my knowledge. Thumbs up!
Hi Wayne, Thanks so much for the formula, That makes perfect sense. I can try that one as well in some of my workbooks. Thanks for following and I look forward to creating many more applications right here.
THAT'S GREAT.
HELLO, DO YOU KNOW HOW TO USE CONDITIONAL FORMATING TO HIGHLIGH ROWS AND COLUMNS WHEN MULTIPLE CELLS ARE SELECTED. SUCH AS WHEN SELECTED FROM A3 TO D5 WITHIN A RANGE THE COLUMN FROM A TO D AND ROW FROM 3 TO 5 ARE HIGHLIGHTED. THIS NEEDS TO BE DYNAMIC TOO.
THANKS IN ADVANCE.
Thank you so much. I'm wondering whether I'll have to set this new rule every time I open a new worksheet.
HI and thanks Anthony. All code, formulas and conditional formats only need to be set once and saved. It will be available each time you open your workbook. I hope this helps and thanks so much.
Thank you so much.@@ExcelForFreelancers
@@ExcelForFreelancers I'm considering subscribing to the Monthly training program. However, I have no good background of excel (codes and formulae). I am highly motivated to learn Excel applications. I have been trying to learn common functions, I feel I can do something. I thought I could develop some applications to help schools, health centres, small businesses. I see the market is wide open and very few people are concerned about these opportunities. Kindly help
Hi Anthony, thanks so much. It is a great opportunity and great course to teach you how to create and sell your own Excel-based applications. If you are unsure of your VBA skills I have a free VBA course taught by MVP Sumit Bansal on my site right here: www.excelforfreelancers.com/enroll-in-the-free-excel-vba-course-by-mvp-sumit-bansal-of-trumpexcel/
Thank you so much for this! So appreciative
You are so welcome and glad you love it. Thank you for your Likes, Shares & Comments. It really helps.
Very helpful. Thank you for your study.
For sure you are very welcome. Cam On Ban Toàn
Thank you for this! It was helpful for a newbie like me
Great and thanks so much. I also have a new series for beginners every Saturday here ua-cam.com/play/PLIBeRriXvKzDTzNp5IME4-oXXCrIjvV4z.html
Amazing work! Thank you
Thank you so very much, I really appreciate that. Thank you for your Likes, Shares & Comments. It really helps.
No importa a lo que te dediques hay dos cosas que debes aprender si o si: Ingles u Excel. No matter what your job is, there is two things you've to learn: Excel and English. This video was made a long time ago, and it still being useful. That's. I just meet you, and I've a lot of video to see in your channel. From Nicaragua (Central America) regard
Thank you so very much Rafael. Its no problem, I am happy to help and share
what a nice trick, Thank you Randy
You are very welcome Shay, I am glad you liked it.
How would you make the last row dynamic so the format grows as the table expands without having to go in and change the range everytime? Thanks for help
HI thanks you can make the last row dynamic using a variable such as LastRow = Sheet1.Range("B9999").end (xlup).row
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
You could change the range in your code to a large number:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("E5:K999")) Is Nothing Then
Range("A4").Value = Target.Row
End If
End Sub
With the conditional formatting:
=AND($E5"",$A$4=ROW())
the selected row isn't highlighted unless there's a value in column E.
HTH.
Your all videos are amazing sir thank you
For sure you are very welcome. I am happy I can help.
I found out what was wrong, it needed to be Saved Macro Enabled Thanks it now works like a charm
Ok great that makes sense, sure. Please feel free to join our group if you have questions as well: bit.ly/groupexcel
So I’ve been trying to find a way to apply this to many sheets and existing excel workbooks. Would I have to do this process for all the exiting workbooks and worksheets??
If all of the sheets are similar you can use "ActiveSheet" instead of the specific sheet. That should work fine however you will want to make sure that the cell used for the selected row is in the same cell for every sheet. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Great video, thanks guys. I was wondering if there was a way to incorporate the undo function when using this code?
Hi, Undo can also be incorporated as well. In the File Manager, Part 3 I use Application.Undo, in case the user makes an incorrect Drag and Drop. You can check it out here: ua-cam.com/video/gZc4n0SdmHI/v-deo.html
Thankyou so much. It worked good.
You're welcome! Cam on ban :)
Thank you so much, this is another great video. Might I suggest you show us another approach where we have a narrow column on the left where we can toggle the row highlighting on or off? This gives us the freedom to have several rows highlighted.
Hi Tony, thanks so much for your suggestion and comment. That makes a lot of sense. I do have this video which shows how to highlight multiple rows in a table: ua-cam.com/video/Ltx7DLLGGHo/v-deo.html
More excellent training Randy! Much appreciated!
Thank you so very much, I really appreciate that Rob. this is an older one.
Hi Randy, is it possible to make the highlighted Row dynamic. Like when the data rows with value are increasing, the target range is also increasing..?
Hi and yes you can use dynamic named ranges using the offset formula and for conditional formatting you can include a large range for the max rows. I hope this helps and thanks so much.
This helps me a lot. New subscriber here.
Thanks so much and great to have you here Jimmy
thank you so much man for the video very helpful
Glad it helped and thank you for your Likes, Shares & Comments. It really helps.
Do you have a video that shows how to highlight the row and column labels of the active cell? In your video, the active cell row and column labels are highlighted yellow. Do you have a video and/or tutorial on how to do that?
Hi and thanks, I only have active row, however you can use another cell to display the selected column such as
Range("A1").value = Target.Column
then write a new conditional formatting for the "Column()" instead of the Row()
I hope this helps and thanks so much.
Great tutorial Randy. I have a spreadsheet with 5000 rows and this works well until I have to insert a row at the top. Then it does not include the inserted row unless I go back and change the range. Is there a way to make it dynamic? Thanks
With VBA I would just insert the row at the bottom and then resort your list based on something like an 'Added On Date' so this way the newest is always at the top but without having to actually insert any row, which can mess up both conditional formatting and VBA. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Thanks Randy.
I have made a request to join the group
Hi, thanks so much for sharing. It works great in my case except when the worksheet I'm working on is locked. I wonder if there's a way to work around it.
Having a locked sheet is ok, but you have to make sure any cells which changes are being made to (like the selected row #) are unlocked. This is important that these specific cells remain unlocked so VBA can make changes to the cell. WHen a sheet is protected vba cannot make changes to a protected cell
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
@@ExcelForFreelancers Hi.. see my method above using =CELL("row") = ROW() for the conditional formatting formula. This works even if the sheet is protected, as the Worksheet_SelectionChange event does not write back to the worksheet.. so no conflict. It only calculates the worksheet via ActiveSheet.Calculate to update the results of the formula driving the conditional formatting. Give it a try.. hope it helps you. Thumbs up!
This is an interesting use of conditional formatting; it
only works when the sheet is unprotected how can I get it to work when I protect
sheet. Please send link on this topic if there are any
Thanks, keep these tutorials going
HI thanks this will work regardless if the sheet is protected or not as long as you unlock the cell in which the row is being applied or use xlUserInterfaceOnly within VBA (Which allows VBA to make sheet changes but not the user)
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
I've done it on a sheet of my own and when protecting the sheet you just have to allow the sellection of the protected cells. It worked for me. :)
Thanks for the informative presentation. Instead of defining the range of cells in the table, can you define the table as an Excel Table, give it a name, and them use the table name inside both VBA and he Conditional Formatting?
Hi Jack that is correct however I have found using tables to be somewhat restrictive for my purposes. Since we do a lot with data and in my larger applications, data is complete separate than the user interface, I generally don't use tables. Thanks for the comment and input.
Very Informative video. Thanks, Excel for Freelancers
Thanks so much. Glad you liked it.
Gr8 sir, I am searching for this whole day, and I found excet what I want, thank you so much, one little question can we save this as a micro so we can use in which file we want
Thanks again sir
Hi Samir, thanks so much. I am glad you enjoyed the training. You can export or save this module or you can copy and paste it into any other workbook. I hope this helps and thanks so much.
@@ExcelForFreelancers thanks for reply sir, I will try and let you know
Respected Sir Kindly make short video like this which tells us different functions.
HI and thanks. i will do my best to get that added in the near future.
Great job boss...Thank you so much for this helpful video..
For sure you are very welcome Muhammad. I am glad you liked it.
Hello Randy and thank you for the video - it is very helpful and does the trick. There are 2 questions though -
1.as soon as I applied all these steps to my worksheet - the active cell’s row is highlighted all right, but Do and Undo functions stopped working and disabled- to be exact they work only if I edit the cell but don’t hit Enter yet. As soon as I hit the Enter - no more Do and Undo functions.
I figured out it is VBA code doing - as soon as I have it disabled - Do undo are restored.
2. The code works only for mouse double click. It seems to do nothing for first\single click.
Do I do something wrong??? Does it happen to me only or to anybody else as well?? I use W7-64 and MS-Office 2016. Thank you
Hi and thanks, when VBA runs, the clipboard is cleared, preventing the Undo option. The Selection Change event will work for any event when you make a selection change
You may want to consider enrolling in Daniel Strong’s Ultimate Excel VBA 30-hour course, in which I have secured a special discount for Excel For Freelancer Followers right here: bit.ly/VBACourse_Discount
This trick is really very cool.
Thanks so much. I am glad you liked it
ThnX Randy, I have implemented this great trick into my members sheet but I run into a problem. If I do some format changes I can undo those actions before VBA row selection, after VBA the button UNDO is greyed out and can't undo any format change......
ThnX in advance
Hi Jose, VBA does not allow us to Undo actions after VBA has run, however I have created two-part video on a special way on how to get around this right here:
Part 1: ua-cam.com/video/6TnxPvLz_44/v-deo.html
Part 2: ua-cam.com/video/fE15W72vnEE/v-deo.html
I hope this helps and thanks
thank you for this video it was very helpful
For sure, you are very welcome and happy to help and share
Thanks for the well paced and simple explanation.
I've noticed that after implementing this code that I am unable to undo (CTRL+Z). I'm assuming this is because the last action was the VB script?
Is there a way around this?
Hi and thanks, yes this is cause from VBA when its run the history is cleared, You can also try this video to undo changes ua-cam.com/video/6TnxPvLz_44/v-deo.html
Hey this is awesome!
I was wondering if there is a way to do this without a helper cell?
I'm stumbled upon the code, but the logic (i think) would be:
1) Clear formatting of the previously selected row,
2) Color code the newly selected row.
What i can't seem to get around is step 1, how would i capture the previously selected row?
Hi Miaden, thanks very much. I am so glad you like it. There is a way to simply color the selected cell, Row, or Column, using VBA without a helper cell and without conditional formatting, however you would either, have to clear the formatting of all cells in a sheet or use a cell to 'remember' the selected cell in a sheet. So that when you select a cell, you can use a hidden cell to remember such as Range("B1").value = Target.Address, Then when selecting a new cell, you can clear the formatting using the saved cell address.With that said it may not be any easier so the Conditional Formatting option may be quicker and easier. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
@@ExcelForFreelancers Yeah, that makes sense. If there are some colored ranges, it will have to overwrite it, didn't thought if that. :) Thank you!
Great tip and very useful. However, I have noticed that when I click away from the highlighted row and into an area on the worksheet that the macro does not cover, the highlighting does not clear. Am wondering if I made a mistake in the VBA code or not.
Hi and thanks for your comment, You can simply add a 'ClearContents' line for the specific range to clear the contents using an else statement, something like
if Not Intersect(Target,Range("A10")) is nothing then
Range("A1").value = Target.Row
Else
Range("A1").clearcontents
End If
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
@@ExcelForFreelancers Perfect! It worked. Should have thought of that myself...
Great to hear that.
it works fine and very helpful, but ctrl z not working on the selected area, can you help with it
HI and thanks so much. Once macros run the 'Undo' option is cleared in Excel. I hope this helps and thanks so much.
@@ExcelForFreelancers Thanks for the update
Nice its work, Thank you
I am glad you were able to get it to work, Thanks so much.
Thank you very much for this. I greatly appreciate it. What if I want to highlight only active selected cell ?
Just like you add the selected Row # in one cell, you would add the selected column # in another cell. Say that the row # is in B1 and the Column # is in B2, then just change your conditional formatting to this rule:
=AND($B$1=ROW(),$B$2=COLUMN())
Hi, can you show tutorial on how to do the listbox in user form become grid box?and only display selected row header in excel. Really appreciate your help
Hi and thanks I will try to add this in the near future however you can request this in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel
We have 30,000 Members who would love to help you with this. Thanks so much.
@@ExcelForFreelancers look forward to my requested , will do thank you for the info & reply . Love all your tutorial
THAT'S GREAT.
HELLO, DO YOU KNOW HOW TO USE CONDITIONAL FORMATING TO HIGHLIGH ROWS AND COLUMNS WHEN MULTIPLE CELLS ARE SELECTED. SUCH AS WHEN SELECTED FROM A3 TO D5 WITHIN A RANGE THE COLUMN FROM A TO D AND ROW FROM 3 TO 5 ARE HIGHLIGHTED. THIS NEEDS TO BE DYNAMIC TOO.
THANKS IN ADVANCE.
Hi and thanks so much. I am glad you enjoy the content. For selecting multiple rows, you can check out this training video: ua-cam.com/video/Ltx7DLLGGHo/v-deo.html
I hope this helps and thanks so much.
question in highlighting selected rows in a table can this be done in a protected sheet
Hi Roy thanks for your comment. Yes it can, as long as you allow users to select protected cells and make sure that the cell in which the row # is changes is not protected or use xlUserInterfaceOnly within VBA when setting up the protection which allows VBA to make changes to protected cells.
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Thank you Randy, definitely this is amazing trick it's make my work easier 🙂
I am really glad I can help Sanyasi. Thank you
Hey Randy! Very Clever move indeed. I have been using a longer code from cpearson website for this purpose. I am gonna replace that from now on.
Glad it was helpful Imran. Thanks so much.
can you guide us where to learn how to format a cell based on TIME (like before 12 pm format specific color, and between 9 am to 12 pm different, betwwen 11: pm and 4 am different color)
will be much appreciated
HI and thanks very much. Time is simple a number in excel so you can use the < or > to within a formula to determine if it is before or after a specific time. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Just wondering if you would be interested in doing an update on this video to highlight the column as well as the row. I have a speadsheet to keep track of all my Lego parts which is too large to fit onto one screen, and it would help when scrolling across the sheet.
Hi and thanks so much. You can add an additional conditional formatting rule in which you put the selected column in a cell, such as A1 and then you the conditional formatting rule would be
=$A$1=Column()
I hope this helps and thanks so much.
@@ExcelForFreelancers OK thanks for that. I guess that the VBA would work in the same way?
Yes for sure, in VBA you would choose a different Cell and add that column such as
Range("A1").value = Target.Column
(this will set the column # into the cell, then Conditional formatting will take care of the coloring of the column)
I hope this helps and thanks so much.
Thank you and I love your videos making my way though them bit by bit. Again thankyou
Your very welcome. I have a new one every week (hope i dont run out of ideas. LOL )
Thanks for a good video
For sure, you are very welcome Rafsan
Best For ever......................
One Query : - how to highlight particuler cell via drop downlist
" Condition :- 50 Document names in my list , highlight list via Country name & Party name , using Dropdown list.........
Hi thanks you would just have to set up a Conditional formatting based on the selected values within that drop down list and matches within a range. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Randy, what you do is simply amazing. Just started to follow your on-line trainings and wanna keep it on like that. Having seen (and then trying to put it to work on my own) your 'highlight selected row' tutorial I came across a question which is: if you have not just one, but two separate tables on a given worksheet, how do you code VBA to make the rows be highlighted on BOTH tables independantly? I am a pre-beginner VBA dude, and a "VBA for Dummies" won't come handy on the topic...
Tried to duplicate the code as you tought in your video, + changed the target range so the two tables would be distinguished. All I got in return was an error mssg saying "Ambiguous name detected: Worksheet_SelectionChange"...
Tried to use some common sense, but failed...
Can you (or anyone of your followers) help somehow, please?
I am not a FB user, so this is my only chance to look for some support.
Many thanks. Greetings from the other part of globe.
Chris
Hi and thanks for your comment. I am glad you like the training. You would want to use two different cells to keep track of the row and two different selection change ranges when highlighting rows in two different tables, where B1 would set the highlighted row for one table and B2 would set the highlighted row for another table. Perhaps something like this
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("C5:M100")) Is Nothing Then
Range("B1").Value = Target.Row
End If
If Not Intersect(Target, Range("P5:S100")) Is Nothing Then
Range("B2").Value = Target.Row
End If
End Sub
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Thank you!
For sure, you are very welcome
Great as usual.. thank you
Thanks so much Ahmed, I am glad you liked it.
Sooper excellent sir
Sir please make a video on simple excel school management software....
Basic need is school fee collection and balance of each student... And bus fee collection and balance (bus fee change depends on area)
Thanking you sir
Hi thanks I might do this in a course, but it would not be for at least a year since I have a list of courses to create already. Thanks for your patience. You may want to have one of our amazing developers create this for you as a Paid Job by posting in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel
If I wanted to do excel freelancing. What software and key skills would I need to get started.
Hi that's an excellent question. When i started doing Excel Freelancing about 10 years ago I knew only a few Excel skills and almost no VBA.
The basic principal for almost any Freelancing job is that you have to solve a problem for a customer and do it in a manner of value (money) for both you and the customer.
Excel is a great to get that done, however there is a great deal of more than just Excel to learn (Sales, Customers, Marketing, etc)
A great way to start is on Upwork or FIver where you can offer your services. Be specific with the work that you can do and do not promise work that you are not capable of (yet). As you take on more jobs, your skills will grow.
Excel is a Massive application so no one person knows everything about Excel. Some are FOrmula Experts, Others are Pivot Tables, and some love data.
My specialty is taking Excel and creating actual applications from it, then selling those applications (Like this: www.labor-burden-calculator.com )
I will be offering courses (small and large) So please join our Excel for Freelancers Facebook group here: bit.ly/groupexcel and also make sure you sign up to get all the free downloads from my facebook.com/ExcelForFreelancers/ page.
Thanks so much,
Randy
Question - is there a way to create a search feature (with a wild card) where the results appear in the table below?
Hi yes sure you can, feel free to check out my Advanced Filter video here: ua-cam.com/video/YuCrEWU2Tm0/v-deo.html
Excel For Freelancers Will do. Thanks again. Your videos are great ☺️
I tried before another way to achieve this. But I struggled with not being able to copy and paste any longer because of this. So I'm wondering if your way has the same problem of the function of copy and paste being not working any longer .
Hi and thanks very much. This solution will work even if copy and paste is disabled. I hope this helps and thanks so much.
what is the logic for setting '9' in the first place as selected row? is it random a choice?
HI Mufrad, that was just a random row within the table to set the initial conditional formatting, however when a user selects a row in a table, it will change. Thank you for your Likes, Shares & Comments. It really helps.
I want to highlight those rows which are having same values in cells in perticuller column. Example column d is having post codes.So I want to hightlight active rows when same post code is there in D column
Hi thats a great question. There are a few good ways to do that. I have a group of 5,000+ Excel Experts on Facebook, Can you please post your question, along with your screen shot or workbook, there so we can help you get the answer you need right away? bit.ly/groupexcel
For selected columns, how can we do? A1=COL() like kinda of?
Hi and thanks you can do something like Sheet1.Range("A1").value = ActiveCell.Column or Sheet1.Range("A1").value = Target.Column
Pls suggest technique to use this method by protecting the cell.
Hi thanks when you protect a worksheet you can set the 'select locked cells' which will allow you to make a selection of locked cells, Just make sure that the cell in which vba places the row # is unlocked.
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Thanks Randy. This works. I will be connected with you & will definitely post you if any additional help require. Once again thanks for your quick response. Your tutorial is very helpful.
Do you have a course that starts from beginner to expert in Excel?
I have not created a course in beginning Excel, simply because there are hundreds of courses that teach this very well already.
thank you so much sir for your video but I receive an error said that the argument is not optional if you let me know how to fix this problem I would really appreciate it thank you again
Hi thanks it would be difficult to know the issue without seeing the code and the workbook. Can you upload it to our Excel For Freelancers Facebook Group here: bit.ly/groupexcel
We have 25,000 Members who would love to help you with this. Thanks so much.
Nice... But when i protect sheet it dose not work please make any solution for it. Thanks
Hi thank you will need to remove the protection on any cells that are changed by the user or VBA. Thanks very much. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Great little hack, however the 'undo' (ctrl+z) command is disabled when the formula is active? how do you get around this?
HI Robert, Thanks for your comment. I have a video training and download that shows you how to create your own log history with Undo (since VBA automatically clears the clipboard when a macro is run). Here is the two part training ua-cam.com/video/6TnxPvLz_44/v-deo.html
Dear sir I have issue that when I save this Excel work book after applying VBA programming then it is not happening.give some tips on that
Hi and thanks. Make sure you save it as an 'xlsm' file type so that the macros are saved. I hope this helps and thanks so much.
IS there a way that i can have this work in all sheets in the same workbook without have to recode it
Hi and thanks you can use ActivSheet on any macro and also copy and paste the selection code in each sheet you want to have the feature.
I hope this helps and thanks so much.
Nice!
I'm having problems with this:
Range("Sheet2!B4").Value = Target.Row
Any help? Thanks.
Hi please try "Sheet2.Range("B4").value = Target.Row
I have a free VBA course taught by MVP Sumit Bansal on my site right here: www.excelforfreelancers.com/enroll-in-the-free-excel-vba-course-by-mvp-sumit-bansal-of-trumpexcel/
@@ExcelForFreelancers Worked great, thanks!
Is it possible to have the same functionality without using VBA or Marco?
Not with this type of selection change because the macro places the selected row in a cell which is used for conditional formatting. Thank you for your Likes, Shares & Comments. It really helps.
@@ExcelForFreelancers thanks for replying. Another way to have such functionality in excel without using VBA or Marco
Thank you sir
For sure, you are very welcome
Great Video. But how to Control Z in case we have made any mistake?
Hi and thanks so much. When VBA is run, the clipboard is cleared, therefore you are unable to use Ctrl + Z.
I hope this helps and thanks so much.
Is there any way or command we can write in a code?
HI and yes, inside the VBA Worksheet_Change event you can write
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
This must be the first lines of code, right after the Worksheet_Change event line and before any other code.
I hope this helps and thanks so much.
I have used this coce in your One click view file but it was showing Invalid Outside Procedure.
I have copied this code in sheet(Setup) just above Private Sub worksheet_change(byval target as range).
Let me know if I have to paste it somewhere else.
The purpose of undo or control z is because if mistakenly I have removed something, I can get that thing back as we use to get by doing control + Z
Is it possible to do this without losing copy-paste ability?
HI using Selection Change or running the code, generally clears out the clipboard however if there are specific copy/paste options you would like to perform you can probably do it through a macro. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Sir, I have pivot table where is text as value with subtotal. How can I auto hide row for subtotal on items that i don't want to show in pivot table when select change slicer
Hi , Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 50,000 Members who would love to help you with this. Thanks so much.
Can you show this on Google Sheet ??
Hi and thanks for your comment. I am exclusively focused on Excel-based applications. I hope this helps and thanks so much.
If I filter the data in the table then remove filter highlight works only in the first cell on selected row and other cells in the same row not highlighted , please advise
The highlighted row, is based on a Row Number, therefore if you filter the data, the rows may be hidden or reorganized. Also make sure that the Row Number is not cleared on filter. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question with code screenshots here:bit.ly/groupexcel
Is this method possible in google sheet ??
HI Ashik, thanks for your comment. It might be however I do not use Google Scripts you would have to research that.
Great, thank you so mach
Thank you very much. I do appreciate that.
Good job
Thank you very much. I do appreciate that.
How to if I want mouse point to $C$3 but select highlight at row 10 by has a formula at $C$3? Thank you for you answer.
Hi and thanks can you post your question, with screenshots, in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel
We have 40,000 Members who would love to help you with this. Thanks so much.
Thanks for sharing
For sure you are very welcome Syed
I don't have Excel access until tomorrow, but could you do without the A$ cell being updated by the macro and just refer to target.row in the conditional format rule?
Hi Scott, the conditional format is based on a specific row. That row has to be placed somewhere so the conditional formatting rules will recognize which row to highlight. I hope this helps and thanks.
@@ExcelForFreelancers as in, conditional formatting cannot use excel object methods?
This is the best I have found so far, if you find a better way, then certainly use whatever method you like
What should we do for using in multiple worksheet on the same workbook? Also, after saving the file, close and open, the VBA stops working and just leaves a row "conditionally formatted"?
Hi Matthew, thanks for your comment. You can copy and past the code into any sheets. Also please make sure to save the file as an xlsm or xlsb type so that macros are saved as well. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
@@ExcelForFreelancers Thank you! I have been playing with this and VBA. I made two workbooks and tried to add this to "simple trick" to another file and I watched this three more times before I asked myself. "I wonder if he replied to my comment?" LOL Thank you! I just applied to the FB Group! [I was using the wrong file type].
hmmm.... still a problem... says I'm out of memory (error 7). :( Once accepted I will try to post to group. Thanks again!
Nice present
Thank you so very much, I really appreciate that. :)
One thing I spotted while using this vba. When the user is pressing select all in the upper left area, a bug appears.
In order to solve that, after the if condition, I entered: If Target.Rows.Count > 1 Then Exit Sub
Probably the case was already solved on facebook group or something. Just in case for those that don't know it yet :)
Hi Dragos, yes you are correct. In most of my workbooks I now use If Target.Rows.CountLarge > 1. This also fixes the problem. Thanks for catching this.
i tried this process but it doesn't work when the sheet is protected, any tips in order to fix this issue? thanks and more power to your channel!
Hi Thanks for your comment and question. You have a few options to change the protection or unprotect & reprotect within the code For additional questions on Excel or VBA, please join our Facebook Group, as well have 7000 Excel experts who can help you here: bit.ly/groupexcel