This was a great tutorial, thank you. I ran into one problem though, as we color code some of the rows in our worksheets to indicate status of that item. I can no longer select a row (or a cell) and fill it with a specific color when I choose to do so. Is there a way to run this macro/vb and allow me to purposely select a row and fill with the color of my choice? Or is it an either or situation? The active cell VB is helpful for viewing during a meeting, but the specific selection of color fill is helpful for tracking at another time, if that makes sense. Any advice or info appreciated. Thank you!
Yes, you could write code to choose a specific color or get the color from a range of colors. You could even pre-color some cells to the right or left and have the user select a color before running the macro.
@@MyExcelOnline i have also tried the sample file and the functions do not work on them either. i wonder if there is a system setting blocking macros or something
That is possible. There is the Trust Center in Excel that you may need to enable. You can do that by putting the Excel file in a folder that you want to use and then open up Excel and go to File|Options. Go down to Trust Center then Trust Center Settings. Pick Trusted Locations and then Add New Location and select the folder where you put the Excel file above. Hope that helps!
Thanks for this info - exactly what I'm looking to do. When I try to run the macro, I get a Run-time error '1004' which I then select "debug". It highlights the Selection.Interior.Color = vbBlue line. Any idea what I'm doing wrong? Thanks!
Hi. Sure, you could probably just get the actual number for vbBlue. You can do this by recording a macro, turning the color of the cell to the color you would like, stop the macro and see what it wrote and then paste that line into the one above. Or, you could try downloading the practice file (link in description). Hope that helps!
@@MyExcelOnline Thanks for the help... it ended up being that I had protected cells in my spreadsheet - that caused the error. 🤯 All fixed now! Thanks again for the help with this!
Yes you can! You could try something like this for the Bottom (you can also replace bottom with top, left or right) Range("A1").Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous Range("A1").Borders(xlEdgeBottom)..Color = RGB(255, 0, 0)
You're welcome. Thanks for watching! In that case, you could set a range variable to the current selection. Try this: Dim rng as Range Set rng = Selection
Thanks alot it worked for me. I just need one assistance. I want to make some cells background colours remain unchanged even if they become active cells. I need your assistance in this. Please if you can guide thanks.
So glad it worked for you. Yes, we can help you. If you go here: www.myexcelonline.com/microsoft-excel-consulting-services/ you can ask our consulting services to help you be more specific in what you are hoping to accomplish.
Thank you for the great video. I wanted to ask. Lets say once the cell is clicked and changed color. Is there a way to re click on that cell to remove the color and back to its original state
Yes. You would need a variable to keep track of the previous cell and then change that cell to no color when you change the color of the current cell. Hope that helps!
Can you make a drop-down list of 3 colors to choose one color? I want to color every cell with either green, orange or red. The cells have different text in them. Thank you
I got this working alongside other options. Now the only problem im having, (i use it to find boxxes in the rack) i work from a searchlist that links to the other sheet, now does this active cell color only work when the sheet is allready clicked in. So in short If i open excel search the part i need i click the link and excel redirects me to the cell i need but the color only starts working the selection after that one How do i fix this
I think you would have to write another function for the initial selection. If you would like help with a specific sheet, feel free to go here: www.myexcelonline.com/microsoft-excel-consulting-services/
@@MyExcelOnline thanks for your reaction, i use it for something at work but its my own thing to fiddle with this so unfortunately i can't get funding for it. Thanks anyway ill keep on searching and trying
Hello! Is there a way when you click on a cell, that only changes the color of the cell border while it is clicked on so I can just look at the sheet and see the selected cell easier because it is a different color while clicked on and when I click off the cell it is back to normal?
Yes. in VBA instead of changing the backcolor, you could change the borders like this: rngRange.Borders(xlEdgeBottom).LineStyle = xlContinuous rngRange.Borders(xlEdgeBottom).LineStyle = .Weight = xlThin And you could repeat that for xlEdgeTop and xlEdgeRight and xlEdgeLeft You can also set the previous cell back to the original color if you would like. Hope that helps!
I’m facing a problem, if i have a coloured cell already then I activate this feature in this vid .. once I select this coloured cell then move .. the old color will disappear. So please your advice
Oh, that's interesting... You could set a variable to keep track of the old color and instead of changing it back to transparent, you could set it back to the old cell color value. Hope that helps!
its work, but... when i block some cells, that can't blocked, & can't undo the value of cell or other backward step, its like unnormal excel, how to normally work, but still use the macro?
Thanks for the question. I believe some tasks done via VBA do not allow for Undo. In other words, once the code has been run, Undo is no longer an option.
You may need to reapply the conditional formatting via VBA after the VBA code runs. It depends on how you have that formatted. You could record a macro and then look at the code to see how that is done.
Yes. You can either copy the macro into the file where you want to use it or you can leave the file with the macro already in it open and you should be able to go to the macro menu and select the function from the open file with the macro.
Do you know hot change color of cell frame when we search excel file? Sometimes when i search something in excel I can't see result because the cell frame color is light green and can't find it because of black color of tables.
Hi! I think you can select all the cells with CTRL+A and then right click click and go to Format Cells. Then click on border and change the color to something you would like and then click Outline and Inside. You can also change the cell fill color here on the Fill tab. Hope that answers your question.
@@MyExcelOnline Thanks, but I need solution when I search some text in Sheet and excel find this cell, but I can't recognize where it is, because searched cell frame is some kind of light green color and I can't see it. I need this cell frame to be red colored in order to recognize imidiatelly.
OK. That sounds a little more specific than what we are showing here. If you would like further help, please check out our consulting services where we can help with specific questions: www.myexcelonline.com/microsoft-excel-consulting-services/
i don't see the "Option Explicit" in my VBA window; also i previously tried a different approach to highlighting an active cell which worked fine except it disabled the "undo" and "redo" functions.
OK figured out the Option Explicit, but similar to other techniques I used to highlight an active cell, once I perform an action, the undo and redo functions are disabled; is there a fix for this?
Great video. My first time to find your channel. I would like to have a color that is faint as opposed to the bold blue or red. What is the code for other color options?
Hi! Thanks for watching! You could record a macro that changes the color of the cell to the color you would like and then look at the VBA code for that color and then change the vbRed or vbBlue to that code. Hope that helps!
I cannot stand the white background in excel. I have changed the color of the background. However, As I enter data, the field (not the font) being entered turns white while typing. How do I code to change the temporary color from white while typing?
Great question! You could try by selecting all cells with CTRL+A and then going to Home → Styles → Cell Styles → Normal → Modify. From there, select the neutral yellowish color. Or some of the other options may work as well. You can also add a custom color there. Let us know if that works!
@@MyExcelOnline This does the trick of not having the cell present with white background--while typing--. However, when I select print, the color of the background now prints as well. To be clearer....Initially I used powerpoint to select a color for the background and save it as a png. Then I would select the color for the background. When I select print, the background color does not show and does not waist in. The only problem is that when typing, the cell presents as white when typing and when you complete entry and leave the cell, it turn the color of the background (which is the good part). I just hate that the cell is white *while typing*. Any suggestions?
I would suggest you contact out consulting services for help with such a specific issue. The link to that is: www.myexcelonline.com/microsoft-excel-consulting-services/
how does the line " option explicit show-up? did u skip a step? Is my Excel version the problem? it has to be an easier way. Are you telling me Microsoft in 20 years do not have this developed?
