Use this SIMPLE TRICK to Highlight a Selected Row in Microsoft Excel

[@ExcelForFreelancers]

🎄 𝗦𝗮𝘃𝗶𝗻𝗴𝘀 𝗔𝘀 𝗕𝗿𝗶𝗴𝗵𝘁 𝗔𝘀 𝗖𝗵𝗿𝗶𝘀𝘁𝗺𝗮𝘀 𝗟𝗶𝗴𝗵𝘁𝘀 - 𝗨𝗽 𝗧𝗼 𝟱𝟱% 𝗪𝗮𝗶𝘁𝗶𝗻𝗴 𝗙𝗼𝗿 𝗬𝗼𝘂! 👉 www.excelforfreelancers.com/XmasSale2024_YTPinnedComm

[@roberthunt1703]

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.

[@ExcelForFreelancers]

Wow, thank you! I am so glad you enjoyed the video, and keep learning. You are doing great Robert.

[@ilocomocomediaentertainmen7425]

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.

[@ExcelForFreelancers]

Thanks so much Iloco, I do appreciate that.

[@marcellejoubert4256]

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!!!!!

[@ExcelForFreelancers]

For sure, you are very welcome and thanks so much.

[@sasavienne]

This is my second comment after I tried this on my own spreadsheet I am so grateful.. You are a star 🌟. 👍 Thumbs up..

[@ExcelForFreelancers]

Great to hear that K D. Keep up the great work and learning.

[@sasavienne]

This is an amazing technique, I wanted to learn since a long time. You presented it in a clear and excellent way. Thanks indeed.

[@ExcelForFreelancers]

I am so glad this training helped you. Thanks a lot.

[@carmela6740]

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.

[@ExcelForFreelancers]

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.

[@sduce4733]

You are the best teacher out there, and thanks for the videos they very helpful and please keep us updated again Thanks

[@ExcelForFreelancers]

Thank you so much Seichy, I am so glad you like the training.

[@sduce4733]

Honestly I think you're the best

[@ExcelForFreelancers]

Wow, thanks so much. Very kind of you.

[@azizniazi9159]

Respected Sir ! you are really one of the greatest teachers.

[@ExcelForFreelancers]

Thank you so much Aziz, I really do appreciate that.

[@wayneedmondson1065]

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!

[@ExcelForFreelancers]

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.

[@ivaniffah1039]

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.

[@unisoft5956]

Best ever use of conditional formating to higlight the row

[@ExcelForFreelancers]

Thanks very much. I have seen so many people do it different ways, however this just seemed the quickest and easiest

[@mohanpuri9917]

This was a very good video. Simple way to highlight the row or column. Great work and good explanation.

[@ExcelForFreelancers]

Thanks so much Mohan. I am really glad you liked it.

[@kwenahlako2987]

Thank you Randy, sweet and short and to the point.

[@ExcelForFreelancers]

For sure, you are very welcome Kwena

[@defenderoff-road]

I found out what was wrong, it needed to be Saved Macro Enabled Thanks it now works like a charm

[@ExcelForFreelancers]

Ok great that makes sense, sure. Please feel free to join our group if you have questions as well: bit.ly/groupexcel

[@CarMaestre]

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.

[@ExcelForFreelancers]

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.

[@rnunez047]

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

[@ExcelForFreelancers]

Thank you so very much Rafael. Its no problem, I am happy to help and share

[@setkiply3176]

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.

[@ExcelForFreelancers]

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.

[@liquidapathy82]

Thank you so much. I absolutely love this technique

[@ExcelForFreelancers]

Great, thanks so much. I am so glad to help and share

[@abdulsafwan711]

Is that possible to apply conditional formula for example highlight selected row by vba code

[@ExcelForFreelancers]

HI and thanks yes it is possible. I show you how to do that in this video: kzbin.info/www/bejne/bILKqmSOi6-Mnrs Thank you for your Likes, Shares & Comments. It really helps.

[@tonyosime9380]

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.

[@ExcelForFreelancers]

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: kzbin.info/www/bejne/gqXbaHeCgax6ftE

[@fahadkafrawi7996]

Amazing work! Thank you

[@ExcelForFreelancers]

Thank you so very much, I really appreciate that. Thank you for your Likes, Shares & Comments. It really helps.

[@jimmycarmelotes2602]

This helps me a lot. New subscriber here.

[@ExcelForFreelancers]

Thanks so much and great to have you here Jimmy

[@nunyabusiness4516]

Thank you so much for this! So appreciative

[@ExcelForFreelancers]

You are so welcome and glad you love it. Thank you for your Likes, Shares & Comments. It really helps.

[@macman007747]

More excellent training Randy! Much appreciated!

[@ExcelForFreelancers]

Thank you so very much, I really appreciate that Rob. this is an older one.

[@shayvy357]

what a nice trick, Thank you Randy

[@ExcelForFreelancers]

You are very welcome Shay, I am glad you liked it.

[@piterfinacze131]

Thank you for this! It was helpful for a newbie like me

[@ExcelForFreelancers]

Great and thanks so much. I also have a new series for beginners every Saturday here kzbin.info/aero/PLIBeRriXvKzDTzNp5IME4-oXXCrIjvV4z

[@ToanNguyen-lb8fv]

Very helpful. Thank you for your study.

[@ExcelForFreelancers]

For sure you are very welcome. Cam On Ban Toàn

[@hoangphamanh6245]

No comments, it’s really helpful. Just wonder that we can select multiple rows in Listbox (userform) and delete them at the same time.

[@ExcelForFreelancers]

Hi and thanks. Yes for sure we can. You could do something like this Private Sub ProcessSelectedItems() Dim i As Integer Dim selectedItem As String ' Loop through each item in the ListBox For i = 0 To Me.ListBox1.ListCount - 1 ' Check if the current item is selected If Me.ListBox1.Selected(i) Then ' Get the selected item selectedItem = Me.ListBox1.List(i) ' Do something with the selected item Debug.Print selectedItem ' Example: Print the item to the Immediate Window End If Next i End Sub I hope this helps and thanks so much.

[@hoangphamanh6245]

@ I created a Userform like userform in your Airbnb Template. It just can delete 1 row at a time. I decided to created a checkbox and let code it as when it is true, then allow select multiple rows and delete multiple rows. If it is false, then just single select and delete 1 row. However, I could not get the delete multiple rows to work in this case. Could you give me a hint? Thank you very much!

[@ExcelForFreelancers]

Hi and thanks, I will try to create something in the future, however if you have tried and run into problems, please post your issue in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 60,000 Members who would love to help you with this. Thanks so much.

[@datdaotrong]

Thankyou so much. It worked good.

[@ExcelForFreelancers]

You're welcome! Cam on ban :)

[@avkhan7892]

Your all videos are amazing sir thank you

[@ExcelForFreelancers]

For sure you are very welcome. I am happy I can help.

[@saqibabbasi3274]

Very Informative video. Thanks, Excel for Freelancers

[@ExcelForFreelancers]

Thanks so much. Glad you liked it.

[@filipdemski8303]

Thank you!

[@ExcelForFreelancers]

For sure, you are very welcome

[@rafsanahamedridoy2853]

Thanks for a good video

[@ExcelForFreelancers]

For sure, you are very welcome Rafsan

[@younessmajjaji5388]

thank you so much man for the video very helpful

[@ExcelForFreelancers]

Glad it helped and thank you for your Likes, Shares & Comments. It really helps.

[@arthurstuartleo1998]

thank you for this video it was very helpful

[@ExcelForFreelancers]

For sure, you are very welcome and happy to help and share

[@mohammadsafiqulislam5779]

Nice its work, Thank you

[@ExcelForFreelancers]

I am glad you were able to get it to work, Thanks so much.

[@awesh1986]

This trick is really very cool.

[@ExcelForFreelancers]

Thanks so much. I am glad you liked it

[@tannertucker22]

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.

[@ExcelForFreelancers]

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

[@tannertucker22]

@@ExcelForFreelancers Thank you.

[@muhammadsameer4776]

Great job boss...Thank you so much for this helpful video..

[@ExcelForFreelancers]

For sure you are very welcome Muhammad. I am glad you liked it.

[@anthonygikuri]

Thank you so much. I'm wondering whether I'll have to set this new rule every time I open a new worksheet.

[@ExcelForFreelancers]

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.

[@anthonygikuri]

Thank you so much.@@ExcelForFreelancers

[@anthonygikuri]

@@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

[@ExcelForFreelancers]

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/

[@leemartin8528]

Great video, thanks guys. I was wondering if there was a way to incorporate the undo function when using this code?

[@ExcelForFreelancers]

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: kzbin.info/www/bejne/nYvGZaFmiMmgfqs

[@webz2k]

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

[@ExcelForFreelancers]

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

[@robertprahl1471]

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.

[@owfaa199]

Great as usual.. thank you

[@ExcelForFreelancers]

Thanks so much Ahmed, I am glad you liked it.

[@beingaslamansari]

Good job

[@ExcelForFreelancers]

Thank you very much. I do appreciate that.

[@SyedMuzammilMahasanShahi]

Thanks for sharing

[@ExcelForFreelancers]

For sure you are very welcome Syed

[@All_about_e]

Great, thank you so mach

[@ExcelForFreelancers]

Thank you very much. I do appreciate that.

[@imranbhatti8580]

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.

[@ExcelForFreelancers]

Glad it was helpful Imran. Thanks so much.

[@samirawati1975]

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

[@ExcelForFreelancers]

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.

[@samirawati1975]

@@ExcelForFreelancers thanks for reply sir, I will try and let you know

[@dencesista2869]

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..?

[@ExcelForFreelancers]

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.

[@ExcelSanyasi]

Thank you Randy, definitely this is amazing trick it's make my work easier 🙂

[@ExcelForFreelancers]

I am really glad I can help Sanyasi. Thank you

[@umarfarooq313ullal]

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

[@ExcelForFreelancers]

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

[@indianmonster1445]

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.........

[@ExcelForFreelancers]

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

[@azamali7392]

Respected Sir Kindly make short video like this which tells us different functions.

[@ExcelForFreelancers]

HI and thanks. i will do my best to get that added in the near future.

[@stevennye5075]

very good

[@ExcelForFreelancers]

Thank you very much. I do appreciate that.

[@krystal4750]

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??

[@ExcelForFreelancers]

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

[@Posnaniensis]

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

[@ExcelForFreelancers]

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

[@nayandavnath1169]

Nice videos

[@ExcelForFreelancers]

Thank you so very much, I really appreciate that Nayan

[@AZPoolCarePros]

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?

[@ExcelForFreelancers]

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.

[@onedaytradingcrypto2758]

Nice present

[@ExcelForFreelancers]

Thank you so very much, I really appreciate that. :)

[@saddamalif1100]

Amazing

[@ExcelForFreelancers]

Thank you so very much, I really appreciate that Alif

[@ExcelForFreelancers]

⚡ 𝗦𝗜𝗠𝗣𝗟𝗜𝗙𝗬 𝗖𝗢𝗠𝗣𝗟𝗘𝗫 𝗘𝗫𝗖𝗘𝗟 𝗧𝗔𝗦𝗞𝗦 𝗜𝗡𝗦𝗧𝗔𝗡𝗧𝗟𝗬 𝗪𝗜𝗧𝗛 𝗘𝗫𝗖𝗘𝗟 𝗔𝗜 𝗧𝗢𝗢𝗟𝗣𝗔𝗖𝗞 𝗔𝗗𝗗-𝗜𝗡 👉 www.excelforfreelancers.com/ai-toolpack

[@mdsaiful6294]

Nice video good

[@ExcelForFreelancers]

Great, thanks so much.

[@jackmandu]

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?

[@ExcelForFreelancers]

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.

[@markls319]

Thank you and I love your videos making my way though them bit by bit. Again thankyou

[@ExcelForFreelancers]

Your very welcome. I have a new one every week (hope i dont run out of ideas. LOL )

[@TheRapidhome]

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

[@ExcelForFreelancers]

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

[@renatajesus6769]

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. :)

[@mannoy9526]

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.

[@ExcelForFreelancers]

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

[@wayneedmondson1065]

@@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!

[@joseborregodiaz4836]

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

[@ExcelForFreelancers]

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: kzbin.info/www/bejne/bIXRqYOsgd-SapY Part 2: kzbin.info/www/bejne/nHaUZoptZ9uhe6c I hope this helps and thanks

[@sonjakillingsworth6322]

Question - is there a way to create a search feature (with a wild card) where the results appear in the table below?

[@ExcelForFreelancers]

Hi yes sure you can, feel free to check out my Advanced Filter video here: kzbin.info/www/bejne/j6amo3iNipeHo5I

[@sonjakillingsworth6322]

Excel For Freelancers Will do. Thanks again. Your videos are great ☺️

[@AbbassiTarek]

Thank you very much for this. I greatly appreciate it. What if I want to highlight only active selected cell ?

[@ExcelForFreelancers]

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())

[@fiqah2907]

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

[@ExcelForFreelancers]

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.

[@fiqah2907]

@@ExcelForFreelancers look forward to my requested , will do thank you for the info & reply . Love all your tutorial

[@royshook2208]

question in highlighting selected rows in a table can this be done in a protected sheet

[@ExcelForFreelancers]

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

[@nsmdr1]

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

[@ExcelForFreelancers]

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

[@nsmdr1]

Thanks Randy. I have made a request to join the group

[@richardfairhead1561]

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.

[@ExcelForFreelancers]

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.

[@richardfairhead1561]

@@ExcelForFreelancers OK thanks for that. I guess that the VBA would work in the same way?

[@ExcelForFreelancers]

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.

[@chiragponda10]

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

[@ExcelForFreelancers]

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

[@forhadkhan3101]

Nice

[@ExcelForFreelancers]

Thank you so very much, I really appreciate that Forhad

[@Bukhari1986]

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

[@ExcelForFreelancers]

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

[@VoVietToan]

thank you !

[@ExcelForFreelancers]

For sure, you are very welcome. Không có chi

[@controlz2010]

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

[@ExcelForFreelancers]

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

[@mufradtanveer6695]

what is the logic for setting '9' in the first place as selected row? is it random a choice?

[@ExcelForFreelancers]

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.

[@integrator6750]

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

[@ExcelForFreelancers]

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

[@hariharagupta5638]

it works fine and very helpful, but ctrl z not working on the selected area, can you help with it

[@ExcelForFreelancers]

HI and thanks so much. Once macros run the 'Undo' option is cleared in Excel. I hope this helps and thanks so much.

[@hariharagupta5638]

@@ExcelForFreelancers Thanks for the update

[@W3DRM]

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.

[@ExcelForFreelancers]

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

[@W3DRM]

@@ExcelForFreelancers Perfect! It worked. Should have thought of that myself...

[@ExcelForFreelancers]

Great to hear that.

[@MladenDJoleJevtic]

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?

[@ExcelForFreelancers]

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

[@MladenDJoleJevtic]

@@ExcelForFreelancers Yeah, that makes sense. If there are some colored ranges, it will have to overwrite it, didn't thought if that. :) Thank you!

[@thegribbs]

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?

[@ExcelForFreelancers]

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 kzbin.info/www/bejne/bIXRqYOsgd-SapY

[@HoaNguyen-xc2kt]

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

[@ExcelForFreelancers]

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.

[@osamasaid8476]

great

[@ExcelForFreelancers]

Thank you very much, I really appreciate that Osama

[@ivaniffah1039]

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.

[@ExcelForFreelancers]

Hi and thanks so much. I am glad you enjoy the content. For selecting multiple rows, you can check out this training video: kzbin.info/www/bejne/gqXbaHeCgax6ftE I hope this helps and thanks so much.

[@henryhache5509]

Nice! I'm having problems with this: Range("Sheet2!B4").Value = Target.Row Any help? Thanks.

[@ExcelForFreelancers]

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/

[@henryhache5509]

@@ExcelForFreelancers Worked great, thanks!

[@robertgregory55]

Great little hack, however the 'undo' (ctrl+z) command is disabled when the formula is active? how do you get around this?

[@ExcelForFreelancers]

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 kzbin.info/www/bejne/bIXRqYOsgd-SapY

[@Chidds]

How can you do this purely in a VBA (conditional formatting and all) so when individual cells within the range are moved it doesn't impact the desired effect?

[@ExcelForFreelancers]

Hi and thanks you can color the background via VBA so that if cells are moved the formatting stays with the cell. I hope this helps and thanks.

[@jitendrasonarkar2582]

@@ExcelForFreelancers ok

[@Puffy2411]

What if selection change is allready taken by checkboxes from your last video...where can i put then code for highlight the rows?

[@ExcelForFreelancers]

You can add as much code as you want inside the Selection change. there is no limit. Thanks very much.

[@Puffy2411]

Ok but do i put the name then like sub selection changer 2 ? Because i try to do like that and this didnt work....

[@ExcelForFreelancers]

It would be difficult to know what the problem is without seeing the code. Can you please join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel . We have 16,000 developers who can help you with this. Please post your code, screenshots and all details so we can get you an answer fast. Thanks so much.

[@ScottMattes]

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?

[@ExcelForFreelancers]

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.

[@ScottMattes]

@@ExcelForFreelancers as in, conditional formatting cannot use excel object methods?

[@ExcelForFreelancers]

This is the best I have found so far, if you find a better way, then certainly use whatever method you like

[@kashifabbas6201]

Is it possible to have the same functionality without using VBA or Marco?

[@ExcelForFreelancers]

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.

[@kashifabbas6201]

@@ExcelForFreelancers thanks for replying. Another way to have such functionality in excel without using VBA or Marco

[@matthewwendell3935]

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"?

[@ExcelForFreelancers]

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

[@matthewwendell3935]

@@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].

[@matthewwendell3935]

hmmm.... still a problem... says I'm out of memory (error 7). :( Once accepted I will try to post to group. Thanks again!

[@markls319]

If I wanted to do excel freelancing. What software and key skills would I need to get started.

[@ExcelForFreelancers]

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

[@wimpiebouillon2083]

IS there a way that i can have this work in all sheets in the same workbook without have to recode it

[@ExcelForFreelancers]

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.

[@dive179]

Pls suggest technique to use this method by protecting the cell.

[@ExcelForFreelancers]

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

[@dive179]

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.

[@prabhatrawat4394]

Great Video. But how to Control Z in case we have made any mistake?

[@ExcelForFreelancers]

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.

[@prabhatrawat4394]

Is there any way or command we can write in a code?

[@ExcelForFreelancers]

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.

[@prabhatrawat4394]

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.

[@prabhatrawat4394]

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

[@10_roy]

For selected columns, how can we do? A1=COL() like kinda of?

[@ExcelForFreelancers]

Hi and thanks you can do something like Sheet1.Range("A1").value = ActiveCell.Column or Sheet1.Range("A1").value = Target.Column

[@Saleh.Wardak]

Nice... But when i protect sheet it dose not work please make any solution for it. Thanks

[@ExcelForFreelancers]

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