Tom’s Tutorials for Excel: Color the active cell, row, or column.
Here’s Tom Urtis’ Monday long tip. Any questions? Just leave us a comment.
Sometimes you want to see where you are on the worksheet, or highlight relevant ranges as you navigate, depending on which cell you have currently selected.
In the picture, three examples are shown that format either the active cell only; the active cell’s entire row and column, or the row and column within the active cell’s current region.
These are Worksheet_SelectionChange events. To install this behavior for a worksheet, right-click on that worksheet tab, select View Code, and paste either of the following procedures (but not more than one at a time per worksheet) into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet, then select a few cells to see the effects of the code.
To format the active cell only:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.ScreenUpdating = False Cells.Interior.ColorIndex = 0 Target.Interior.Color = vbCyan Application.ScreenUpdating = True End Sub
To format the entire row and column of the active cell:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub Application.ScreenUpdating = False Cells.Interior.ColorIndex = 0 With Target .EntireColumn.Interior.Color = vbCyan .EntireRow.Interior.Color = vbCyan End With Application.ScreenUpdating = True End Sub
To format the row and column within the current region of the active cell:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.Interior.ColorIndex = 0 If IsEmpty(Target) Or Target.Cells.Count > 1 Then Exit Sub Application.ScreenUpdating = False With ActiveCell Range(Cells(.Row, .CurrentRegion.Column), _ Cells(.Row, .CurrentRegion.Columns.Count + .CurrentRegion.Column - 1)).Interior.Color = vbCyan Range(Cells(.CurrentRegion.Row, .Column), _ Cells(.CurrentRegion.Rows.Count + .CurrentRegion.Row - 1, .Column)).Interior.Color = vbCyan End With Application.ScreenUpdating = True End Sub
Is there a way to do this where you can still make other cells in the worksheet coloured? I noticed when I added this code (terrific by the way, thanks) that any other cells in the workbook that I had “filled” went back to white.
Ta.
Yes! Yes there is! I looked forever, and this is how I did it:
Sub Worksheet_SelectionChange(ByVal Target As Range)
Range(“G4:K:8”).Interior.ColorIndex = 0
Target.Interior.ColorIndex = 8
End Sub
Hola, soy de Chile, yo hice una variante en donde, me pinta de color la linea activa y de otro color la celda activa, aquí les dejo el código.
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = xlNone
ActiveCell.EntireRow.Interior.Color = RGB(255, 145, 145)
Target.Interior.Color = RGB(255, 231, 148)
Application.ScreenUpdating = True
End Sub
THIS DOESN’T WORK IN EXCEL 2013 :((( ANY SOLUTIONS???
Yes it does work in 2013. Please, instead of just saying it doesn’t work, say instead what is not working exactly, what you expect, what your data is, and what you did. “Doesn’t work” does not help anyone to understand the problem.
Thanks so much – yes it did work for me.
However, is there a way where I can get this to be the case automatically each time I open excel 2013?
This is a Selection event procedure, so it automatically occurs when and while the workbook is active. And, a workbook becomes active when it opens.
Thanks Tom,
What I’m really looking for is a way to highlight the active cell border in Excel. Sometimes I have worksheets that have tons of info and it’s difficult to see where the highlighted cell is located. I’d like to change it to a bright color where it stands out in the worksheet. Is there a way to accomplish this? I’ve looked and looked and still no luck. I hope you can help.
Honestly, I doubt this will help you. A cell border is less prominent than a cell’s interior color. Besides, the selected cell, when selected, does not display a color in later versions of Excel.
Be that as it may, you asked, so here it is… stick this in your worksheet module for a red border you can’t see around the active cell:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Borders.LineStyle = xlNone
Target.BorderAround Weight:=xlThick, ColorIndex:=3
End Sub
Tom,
Thanks so much for this code! I need it for when excel isn’t the focus window, and it works perfectly!
Thanks for the follow-up!!
Thank you Thank you! This had made this entire project much easier!!!
Thank you!!! This is great!
This is great. Thank you.
BUT I have a question – It seems that the code applies to only one sheet of a multiple sheet Excel file. Is it possible to apply to code to all the sheets in that file?
Thank you.
You are correct, the examples in this blog post only apply to a single worksheet. That is because the Worksheet_SelectionChange event is a worksheet-level event.
To make the code apply to an entire workbook, you need the workbook-level event Workbook_SheetSelectionChange.
So, first, delete the Worksheet_SelectionChange code you have in the worksheet module.
Then, go into the ThisWorkbook module.
Paste the original code I posted in my example, but this time, in the ThisWorkbook module as I said, substitute the line
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
with
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If you are not sure how lo locate the ThisWorkbook module, see my example here:
http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-preventing-a-workbook-from-saveas/
hI TO FORMAT ACTIVE CELL ON YOU POSTED THE FOLLOWING:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 0
Target.Interior.Color = vbCyan
Application.ScreenUpdating = True
End Sub
HOWEVER I DONT KNOW WHAT TO DO WITH IT, AND THERE IS NO STEP BY STEP INSTRUCTION, CAN YOU HELP ME PLEASE.
SORRY.
See the third paragraph in this blog post where I give directions on how to install the code.
Sometimes I want to see where I am on the worksheet, but the cells are already colored, and bordered, and I don’t want to lose that. When using this code, each cell selected is changed, and does not revert to the original color when navigating away, nor are the borders visible any longer.
Your coding expertise it really nice, and I don’t want to ignore it. I just need the added feature of reverting back to the original cell color and retain the borders.
Lastly, it would be nice to better understand the code parameters. I’d like to consider different colors or border widths.
To maintain the actual formatted properties of a cell or range by temporarily changing (for example) the color of a selected cell, you can use Conditional Formatting instead of changing the cell’s properties.
For example, this Selection Change event will color the active (selected) cell yellow, and then when you deselect that cell, its existing color or format-related property will be retained.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
.Worksheet.Cells.FormatConditions.Delete
.FormatConditions.Add xlExpression, , “TRUE”
.FormatConditions(1).Interior.Color = vbYellow
End With
End Sub
Tom…Currently using Excel 2013. I used your reply posted 9/9/2016 at 5:47am, worksheet level, so that cells already formatted/highlighted aren’t changed. You’re amazing and a life saver! Thank you so much!!!
What a nice message – – thanks, Kyle!!
First off – please forgive the lack of VBA knowledge here…
Where in the VBA code would you put this script. I am trying this, and I keep getting an error.
I can get the first portion to work, but having a hard time with keeping cells with formatting to retain their highlights after clicking them.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 0
With Target
.EntireColumn.Interior.Color = vbCyan
.EntireRow.Interior.Color = vbCyan
End With
With Target
.Worksheet.Cells.FormatConditions.Delete
.FormatConditions.Add xlExpression, , “TRUE”
.FormatConditions(1).Interior.Color = vbYellow
End With
End Sub
Several things can be going on here. One question is what kind of error you are getting. Another question is if you are trying to color the selected cell programmatically with conditional formatting, and if the cell already has conditional formatting or is its color the actual interior fill of the cell. Any further details would help to understand what you are working with, and what is causing the problem (or error) you are seeing.
Tom thank you for your priceless guidances.
I stil am struggling to understand this.
How do i change the color of and active/clicked cell or its rows and columns, while non of my current coloring formatings get cleared.
so i need a temporary color change(not conditional formating as i also want columns and rows of specific cell to color) but i also need my current colors in my sheet to remain as they are.
Id appreciate it.
hi Tom. i pasted this code below to avoid changing the original cell colors but nothing happened on the sheet. looks like nothing took place. any suggestions?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
.Worksheet.Cells.FormatConditions.Delete
.FormatConditions.Add xlExpression, , “TRUE”
.FormatConditions(1).Interior.Color = vbYellow
End With
End Sub
Hi — I’m traveling at the moment but will be in front of a computer layer today. Meanwhile, just to make sure, did you place the code in the worksheet module (not a standard module such as where macros go).
Update, I just tested the code you posted and it worked fine for me, no problem.
Hi Tom Urtis,
I’m trying the above as well, but nothing happens. MS Excel for Office 365 MSO, 32-bit
Tom, disregard. I saw the post about the quotes. I retyped them and it works! Awesome! Thanks.
Thanks for the follow up!
How can I apply this to all existing and future worksheets/files that I open
If all means all, you can accomplish this by establishing a workbook with the selection code you want in the ThisWorkbook module and save that workbook as a .xlam add-in.
I saved the “To format the entire row and column of the active cell:” to the active sheet in “view code” and then saved it as an .xlam add-in. It shows up in the add-in(s) but does not highlight.
Sorry…I’m a newbie to this (but not entirely oblivious). If you could give step by step that would be fantastic. I would like this to be activated when opening an existing file and/or new file. I have a meeting every day with about 100 rows and several columns that I share on a big screen and it’s difficult for people to see what cell Im talking about.
Thanks in advance
HI,
I’ve been trying to find a solution to this issue for a while. Thank you for all your work.
However I have the following issue.
_________________________________________
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 0
Target.Interior.Color = vbCyan
Application.ScreenUpdating = True
End Sub
____________________________________________________
This code works great BUT ONLY if I insert it one sheet at a time. If I insert this into the ThisWorkbook It does nothing!
Likewise, The Ideal code I was looking for was the second one you posted
______________________________________________________
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
.Worksheet.Cells.FormatConditions.Delete
.FormatConditions.Add xlExpression, , “TRUE”
.FormatConditions(1).Interior.Color = vbYellow
End With
End Sub
________________________________________________________
Because in the forms I do use preset preformatted colored cells etc that would be very nice to keep.
BUt as with the other one. when I insert in either sheet or the workbook it doesn’t do anything.
I am using 2013
PS I forgot to add – yes I changed work sheet to work book
Delete all the code relating to this issue that you have, either in the worksheet module(s) and in the ThisWorkbook module.
Then, in the ThisWorkbook module, paste this in, which works:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 0
Target.Interior.Color = vbCyan
Application.ScreenUpdating = True
End Sub
Tom,
Thanks for the quick reply.
I did as you suggested above, worked fine as far as the color highlight of active cell. But it still kills all of the other highlighted cells I would like to keep a different color.
In your ThisWorkbook module:
• Delete that code you currently have in there.
• Copy and Paste this code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
With Target
.Worksheet.Cells.FormatConditions.Delete
.FormatConditions.Add xlExpression, , “TRUE”
.FormatConditions(1).Interior.Color = vbYellow
End With
End Sub
That works like a charm, one note:
After pasting it in I had to delete and retype the quotes around “TRUE” since pasting it in pasted them as fancy quotes.
Thanks.
The double quote problem (also happens with apostrophes) has been a thorn in my side and I’m sure I am not alone. The html interface on some browsers causes this character transformation even though I post the fully functioning code inside the correct tags. I’ve looked into this issue and have not been able to have it be avoided by everyone. If anyone reading this has a suggestion, I’m totally open to any and all ideas
Tom,
WE MUST BE MISSING SOMETHING!?
When I replace the code above with the code at the top of this page I can highlight cells to a different color but now the active cell goes back to the same pale green outline!
If I use the code at the top of the page I can not highlight other cells and keep that color but the active cell is blue.
I want to do both!
Tom,
WE MUST BE MISSING SOMETHING!?
When I replace the code above with the code at the top of this page I can highlight cells to a different color but now the active cell goes back to the same pale green outline!
If I use the code at the top of the page I can not highlight other cells and keep that color but the active cell is blue.
I want to do both! I cant attached a screen shot here to show you.
THANK YOU! Exactly what I needed!
the cyan highlighting first example works great for me, but I can not get the ones that use conditional formatting to work. I get run-time error 424: object required. Pressing DEBUG highlights this line: Worksheet.Cells.FormatConditions.Delete
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
Worksheet.Cells.FormatConditions.Delete
FormatConditions.Add xlExpression, , “TRUE”
FormatConditions(1).Interior.Color = vbYellow
End With
End Sub
Try
Active sheet.cells
Or
just leave out the parent sheet object altogether and start the line with cells….
I have an issue that is somewhat related to this discussion. I have a workbook with multiple worksheets. Every unhidden worksheet has locked cells, that have formulas in them, and unlocked cells where the users can enter information. Four of the nine worksheets have grouped rows. When I open the workbook, the green outline around the active cell is not there. If I go to another worksheet, the green outline returns and will be there when I go back to the original worksheet. But, I want the green outline around the active cell to always be there. Here is the code I currently have for my workbook. Any advice?
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim wsh As Worksheet
For Each wsh In Worksheets
wsh.EnableOutlining = True
wsh.Protect UserInterfaceOnly:=True
wsh.EnableSelection = xlUnlockedCells
Next wsh
Application.ScreenUpdating = True
End Sub
Tom – Thank you for such a wonderful tip! I have tried several times and continue to have problems with the conditional formatting. Would you edit the following code for me, so when I exit the cell, my original formatting will return? Thank you!
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 0
With Target
.EntireColumn.Interior.ColorIndex = 35
.EntireRow.Interior.ColorIndex = 35
End With
Application.ScreenUpdating = True
End Sub
Hi Dawn, thank you for the kind words.
Are you sure that it’s really conditional formatting that you need to restore? The code should not affect that, as it only affects the interior color property of the cell itself, which is different than conditional formatting upon the cell. Maybe you have cells with fill color already established (not by Conditional Formatting) and that would indeed be affected by the code. Take a close look at which method you used for coloring the cells and post back with whichever method it is so the code can be modified.
Is there anyway to achieve this VBA function not lose the UNDO function in Excel?
When VBA events are invoked such as the SelectionChange event, the Undo stack is emptied and there’s no way around that unfortunately. Same as this example on my blog for the Change event:
http://www.atlaspm.com/toms-tutorials-for-excel/toms-tips-for-excel-running-sum-in-same-cell/
The idea is nice.
However this overrides the original celcolor.
If you have color yellow in cel A1 and you go to that cel and go to another cel, A1 is going to be color NONE (no color) again.
If the color is restored after leaving the cel it is perfect.
Is there a function in Exel to change the color & thikness of the active cel? That would solve everything.
Thank you
Check out my reply on September 9, 2016 to this same question from another visitor in the other comments on this page.
Tom, I must be an idiot. I know I need the VBA to format the entire row and column of the active cell which I get in my worksheet and it works great. Then I know I need the Sept. 9 2016 VBA so that my original colour formatting can stay in place. BUT, for the life of me I can’t figure out how to put the two together!! Been trying or hours! Can you please give me one DBA for both and put me out of my misery or tell me how to get both added to my worksheet? Please and thank you so much.
Just to confirm, are you using (or want to use) the code that highlights the entire row and entire column? So, in any of those thousands of row cells and over a million column cells (I know your worksheet is not that populated but I’m just using those numbers for perspective), each cell in your used range for the selected row and column can and probably does have its own format. For example, cell B7 might be colored yellow but cell B8 might be bold and cell B9 might be colored green with italic font. Using cell B7 further as the selected cell, cell J7 might be formatted with a border and currency number, and so on. I just want to understand the possible scope of what you need to be restored, NOT asking you to list any of your cells’ formats, just asking generally if all cells in a selected row and column need to have whatever their format restored to what they all were before you selected a cell that would change all those formats.
I am trying to get active cells highlighted without losing the already colored filled cells. I have tried nearly every suggested code listed above but I can’t get it to work. When I tried the post from April 24th it came up with a syntax error
What does “already colored filled cells” mean?
Do you not want those cells to be highlighted?
And, are your “already colored filled cells” colored and filled with other conditional formatting, or are their interior color properties changed to a different color, such as if you right-click on a cell, select Format Cells, and you choose a color from the palette and click OK.
Thank you for replying so quickly. The cells that are different colors have been formatted by right clicking the cell and choosing the color and there is no other conditional formatting for the colored cells. These colored cells do not need to be highlighted. The worksheet is broken into sections by the colored the cells (basically the colored cells are headers). Below the colored cells in the “A” column are questions. In the columns next to questions are hyberlinks depending on how the question is answered. My hope is to be able to have the active cell (question) highlighted.
For the worksheet where you want this behavior to take place, replace whatever Selection event code in its module there may be, with this.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Interior.ColorIndex <> -4142 Then
Target.Worksheet.Cells.FormatConditions.Delete
Exit Sub
End If
With Target
.Worksheet.Cells.FormatConditions.Delete
.FormatConditions.Add xlExpression, , "TRUE"
.FormatConditions(1).Interior.Color = vbYellow
End With
End Sub
It worked!! Thanks for your help, my team will appreciate it.
Great – – thanks for the follow-up.
Tom,
This morning I was going completely through worksheet and found there were a few cells that didn’t highlight (I had only randomly checked a few yesterday). I can not figure out why a few select select cells are not working. Any ideas???
My code checks to make sure that the cell it selects has no color assigned to its interior color index property. My first thoughts are, for the cells that seem to be not working, the code is probably working but the cells are either merged, or their interior color was changed to white which still means their color index property is not nothing (which is expressed as -4142 in VBA speak as you see in my code). Check those cells carefully to see if they are colored white, and see if any cells are merged; merged cells are the scourge of programming code design.
Beautiful Code-
Can you add Rows and Columns to this one too?
thanks,
With the question “How do I make the Active Cell More Apparent in Excel” This string of code worked great for me. So when I use the find (CTRL F) function the cell stands out with the yellow fill. What a time saver for me and it keeps all the original formating that I use every month. Thanks, MMT
Oh I used this code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Interior.ColorIndex -4142 Then
Target.Worksheet.Cells.FormatConditions.Delete
Exit Sub
End If
With Target
.Worksheet.Cells.FormatConditions.Delete
.FormatConditions.Add xlExpression, , “TRUE”
.FormatConditions(1).Interior.Color = vbYellow
End With
End Sub
Thanks for the follow-up and the code!
Hello Tom,
am new to vb concept, the 3rd solution works for my problem:
To format the row and column within the current region of the active cell , but as an example
active cell is at say E5 (data starts A1 : p50) then the rest part of same column(down next row of active cell) and same row (right next column of active cell) can be of any other color
Hello Tom,
sorry mistakenly clicked post comment, i will continue
..as an example active cell is at say E5 (data starts A1 : p50) then the rest part of same column(down next row of active cell) and same row (right next column of active cell) can be of any other color or same for both col & row and another thing is it possible to have active cell of color-less or earlier user defined color.
thanx
regards,
relio
Not sure I understand. Are you saying that you want all cells of a current region to be a color, except for the active cell which you want to be not colored?
a b c d e f . . ok..say active cell is at “X” i.e C3, now from c1 header to “X”
1 r s t u v w dotted line & from “X” to last cell c5 row down all cells colored,
2 r s t u v w lly. from 3rd row header i.e a3 to Xth position with dotted line & then
3 r s X u v w from Xth position to last cell f3 all cell should be colored.
4 r s t u v w
5 r s t u v w hope you understand.. regards relio
What does “dotted line” mean? A border? Or do you mean an elipsis like …
border
Works great, thanks for your help
Hey Tom,
I was wondering if you could help me with an excel problem. I have read through this feed over and over and think I have the right base code for what I want to accomplish. However, maybe due to the fact that I am a luddite, the code does not seem to work.
Little background:
I am attempting to create a code that can highlight the row and column of a selected active cell, while keeping/not altering any previous formatting. E.g. I have a spreadsheet for a menswear brand where each consecutive season’s row is filled with a different color. AW14 filled blue, AW15 filled yellow, AW16 orange, and AW17 purple. I would like to be able to select a cell in AW16 and have that active cell’s row and column be highlighted without permanently altering the orange color and font once deselected.
I went off of the comment made by Corwyn Sept 21, 2016. I am sure the answer is right in front of me, but being novice, I cannot for the life of me find my mistake. I see that she is basically asking for the same results as I am. I can get the first code to work, as she did, but as formerly stated I would like to keep all initial formatting, while having the ability to actively highlight. I am attempting to use the second code you recommended to her.
I pasted the following code into my ThisWorkbook module for sheet 1:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
With Target
.Worksheet.Cells.FormatConditions.Delete
.FormatConditions.Add xlExpression, , “TRUE”
.FormatConditions(1).Interior.Color = vbYellow
End With
End Sub
Upon returning to my sheet, I get error code:
“Run-time error ‘5’:
Invalid procedure call or argument”
When I hit debug it highlights .FormatConditions.Add xlExpression, , “TRUE” as the problem
Please apologize for the lengthy post, however I wanted to be as detailed as possible. Any and all help would be greatly appreciated.
Best Tyler
Hello Tyler – –
I appreciate the detailed message, so definitely no apologies needed for that. My first question is, by what method is the worksheet already formatted before any code was run — are the cells already formatted by way of Conditional Formatting, or did you (for example) right click any cells, select Format Cells from the pop-up menu, and select a fill color or border or whatever for those initially formatted cells.
Tom,
I tried to use the code with the conditional expression so I keep my active formatting (which is just manual highlight color of a cell) but it doesn’t seem to be working. I love the highlighting of row and column, I often have to run searches for data and the cells only have 3 characters in them, so it’s hard to see the highlighted cell. But I don’t want to ruin the other formatting (which for the most part is dark colors with a white font).
Can you help?
Thanks!!!
Hi Rosie – –
Would the suggested code that I posted on September 9, 2016 (if you scroll through the comments) be of help to you? It uses Conditional Formatting that would not interfere with existing manual formatting. Let me know if that helps.
Hi Tom,
I found this code in another site that was used to highlight the entire active row without removing the color format that it had, I tweaked it a little bit so that it only highlights the active cell.
Here is the code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range(“a17:Z17”), Range(“A7:Z7”)) Is Nothing Then Exit Sub
For Each aCell In ActiveSheet.UsedRange
If aCell.Interior.ColorIndex = 35 Then aCell.Interior.ColorIndex = xlNone
Next
On Error Resume Next
For Each aCell In Application.Intersect(ActiveCell.Cells, ActiveSheet.UsedRange)
If aCell.Interior.ColorIndex = xlNone Then aCell.Interior.ColorIndex = 35
Next
End Sub
*Please let me know what you think and I would like to give credit to the site and person who wrote this but I don’t know if you are okay with it?
Thanks for your constant help and attention!
In the comments to this blog, I posted a solution using conditional formatting to accomplish what you are speaking about.
Regarding this particular code you posted here, I do not know its origin.
Thank You Tom!
Solid Code, just double click on your worksheet in VBA Editor then Paste.
You’re good to go!
Hi Vaseem, thank you!
Hi Tom,
Thanks for the code. I, like others above, was having problems getting the conditional formatting code to work in Excel 2016:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
.Worksheet.Cells.FormatConditions.Delete
.FormatConditions.Add xlExpression, , “TRUE”
.FormatConditions(1).Interior.Color = vbYellow
End With
End Sub
Nothing would change on the screen, but going to Conditional Formatting and Manage Rules, I could see the rule that was created and changing. On a different web page about VB code, someone mentioned the quotation marks being “fancy”. Sure enough, when I deleted the quotation marks that had been copied and pasted with the code and typed new “normal” quotes, the code started working.
Thanks again,
Kevin
Hi Kevin, thanks for the follow-up.
Hi Tom,
Thank you so much for this amazing tip. It worked great when I am working on the excel sheet.
However, when I try to save it it says “the following feature cannot be saved in macro-free workbooks: vb project to sve a file with these features click no, then choose a macro enable fil type in the file type list. to continue sving as a macro free workbook ,click yes.
what do you recommend for the file type that is macro enabled?
Thanks for your nice comment. Save the file as .xlsm (Excel Macro Enabled Workbook).
You are seeing that message because programming code in an Excel workbook will not execute, or even be recognized, in a .xlsx (Excel Workbook) format, which the workbook you first copied the code into probably was at the time.
How do I highlight the row of an active cell with one color and have the active cell in another color without removing (making all of the cells white) all of the other formatting?
See if this does what you want:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
With Target
.Worksheet.Cells.FormatConditions.Delete
.EntireRow.FormatConditions.Add xlExpression, , “TRUE”
.EntireRow.FormatConditions(1).Interior.Color = vbCyan
.FormatConditions.Delete
.FormatConditions.Add xlExpression, , “TRUE”
.FormatConditions(1).Interior.Color = vbYellow
End With
End Sub
Hi Tom,
This is really great. I have a spreadsheet that has a list of customers in one column and their customer number in the next column. Is there a way, when I “CTRL F” for the cells in both columns to be highlighted?
Can you please clarify the relationship of your Ctrl F reference to what you want to have happen. I assume you mean Ctrl+F which shows the Find and Replace dialog box, so what example could you give that should be entered into that dialog box and what then would be colored, and where?
Or, do you mean you want the Ctrl+F keyboard sequence to be a shortcut to do something specific, if so, what? You wrote, “for the cells in both columns to be highlighted”, but which columns and would that be for more than one unique entry in the list for whatever you are either searching for or entering manually.
Hi Tom, I have a workbook which is basically a long-winded survey. There are multiple sheets, each with differing survey questions – some for staff, some for managers, some for clients etc… Column E on each sheet contains a series of around 30 questions and I have used your code to successfully highlight the selected cell in column E – with it returning to the background colour once another cell is selected… So, so far so good 🙂
The thing is that column F has drop down menus for peoples’ responses to the questions. I had applied conditional formatting to column F so as to colour code people’s answers… Your code has however effectively cleared the conditional formatting from column F.
So basically, is there a way to adapt your code so that it only applies to cells selected in column E???
Thx
Hi Dave – –
This tutorial has received a lot of positive comments and requests for modifications, which is great. Can you tell me which rendition of modified code you are using? Just post the entire cell-coloring code you are using please, so I can know how to fit your circumstance for column E, which sounds do-able.
Thanks Tom – am using:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
With Target
.Worksheet.Cells.FormatConditions.Delete
.FormatConditions.Add xlExpression, , “TRUE”
.FormatConditions(1).Interior.Color = vbYellow
End With
End Sub
The code is in the ThisWorkbook module. As I say it works fine, unfortunately its wiping out the conditional formatting rules in column F!!
Thx
Dave
OK, one more question. You said in a prior message that there are multiple sheets with survey questions. Is every worksheet in the workbook (every means every) the same where only column E is to be formatted, or are there any worksheets that would not be a survey type sheet, and if so, what is/are the non-survey worksheet(s) name(s)?.
Hi – The workbook has 9 sheets in total. These are:
Sheet 1: Questions for Managers
Sheet 2: Questions for Staff
Sheet 3: Standards and targets (hidden sheet)
Sheet 4: PID (hidden sheet)
Sheet 5: Scores (hidden sheet)
Sheet 6: Performance Summary
Sheet 7: Action Plan
Sheet 8: Introduction
Sheet 9: Instructions
So column E on sheets 1 & 2 is where I need the formatting. The others are all non-survey sheets.
I should probably mention that sheets 1 & 2 also have a macro which temporarily expands the width of column F so that people can read some lengthy descriptors in the drop down answer box, before answering. I then use conditional formatting to colour code the answers i.e. red, green, amber depending on ho well people are doing…
(For info the reason I want to highlight the question cell is that there are going to be facilitated workshops with staff and managers, where I will project the spreadsheet and host discussions before arriving at consensus answers – so it will help focus everyone in the room on the question being discussed!!).
I had wondered whether I was making a mistake putting the code into the ThisWorkbook module and whether it would be better embedded into individual sheets.
Thx again for your help.
When you say you have a macro, is it really a macro or is it an event procedure, such as a Worksheet-level Change or Selection even procedure.
Also, it is safer to specify the worksheets to include or exclude by using their project codenames. Please verify if tabname Sheet 1’s project codename is Sheet1, and tabname Sheet 2’s project codename is Sheet2, or is either is not, say what the project codenames are of those sheets.
Not sure whether it’s a macro of an event procedure!! The code is embedded in sheets 1 and 2 and temporarily changes the width of column (array) F – the code, which works fone, is as shown below:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myColumns As Variant
Dim myWidthSelected As Variant
Dim myWidthNormal As Variant
Dim iCtr As Long
myColumns = Array(“f”)
myWidthSelected = Array(38, 42)
myWidthNormal = Array(20, 20)
If Target.Count > 1 Then Exit Sub
For iCtr = LBound(myColumns) To UBound(myColumns)
If Intersect(Target, _
Me.Cells(1, myColumns(iCtr)).EntireColumn) Is Nothing Then
Me.Cells(1, myColumns(iCtr)).EntireColumn.ColumnWidth _
= myWidthNormal(iCtr)
Else
Me.Cells(1, myColumns(iCtr)).EntireColumn.ColumnWidth _
= myWidthSelected(iCtr)
End If
Next iCtr
End Sub
The tabnames for the sheets where I want to beable to highlight the questions are
Sheet 1 = Questions for Managers
Sheet 2 = Questions for Staff
Thx
You answered one question but you did not answer the other question, which is, what are the codenames (not tab names but codenames) of the 2 worksheets of interest. Look in the VBAProject window in the VBE to see what they are.
Oops – sorry about that…
so, if I’ve understood correctly, the code-name for the ‘questions for managers’ worksheet is Sheet 1; and the code-name for the ‘questions for staff’ sheet is sheet 2.
So my VBE shows
Sheet1(Questions for Managers)
Sheet2(Questions for Staff)
Sheet3(Standards and targets)
Sheet4(PID)
Sheet5(Scores)
Sheet6(Performance Summary)
Sheet7(Action Plan)
Sheet8(Introduction)
Sheet9(Instructions)
And it’s sheets 1 and 2 that I’m interested in…
Thx
I don’t know what your format conditions are but I used the code you posted to show one way to go about this/
Delete the event code from your worksheet modules and put this in your ThisWorkbook module.
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveSheet.CodeName <> “Sheet1” And ActiveSheet.CodeName <> “Sheet2” Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Dim myColumns As Variant
Dim myWidthSelected As Variant
Dim myWidthNormal As Variant
Dim iCtr As Long
myColumns = Array(“f”)
myWidthSelected = Array(38, 42)
myWidthNormal = Array(20, 20)
If Target.Count > 1 Then Exit Sub
For iCtr = LBound(myColumns) To UBound(myColumns)
If Intersect(Target, _
Cells(1, myColumns(iCtr)).EntireColumn) Is Nothing Then
Cells(1, myColumns(iCtr)).EntireColumn.ColumnWidth = myWidthNormal(iCtr)
Else
Cells(1, myColumns(iCtr)).EntireColumn.ColumnWidth = myWidthSelected(iCtr)
End If
Next iCtr
‘
If ActiveSheet.CodeName = “Sheet1” Then
If Target.Column = 5 Then
Columns(5).Cells.FormatConditions.Delete
With Target
.FormatConditions.Add xlExpression, , “TRUE”
.FormatConditions(1).Interior.Color = vbYellow
End With
End If
End If
End Sub
Works a treat… Just needed to swap your “” for my “”. Oh and I needed to apply the colour coding to sheet 2, as well as sheet 1… All done and working fine…
Thank you so much for your help and if case your were wondering, the workbook is a tool to make sure hospitals take a range of essential steps to reduce potentially preventable deaths of vulnerable patients… so a really important cause… Now I can get back to piloting and evaluating…
Again, thanks loads…
Dave
Hi Tom
Thanks for this code.
Is there a way that I can have the selected row highlighted, only in a restricted range?
In my case, I would like the text in the cells of columns B and C of the selected row to go bold, italic and red, then return to their original formatting.
Thanks
Francis
Hello Francis – –
Conditional formatting can do this, which is shown in other replies I made on this page. But regarding your range of 2 cells wide (columns B and C), is there a restriction of which rows should or should not be involved.
Thanks for the quick reply Tom.
What I’m trying to do is have the text in columns B and C highlight on the active cell row, if I select a cell within the range D4:Q43.
So the selected cell itself I don’t want to highlight. I want that highlight offset to column B and C.
Thanks Tom. I managed to achieve what I wanted using the below:
Option Explicit
Public xfrLastCell As String
Public xfrActiveCell As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range(“D4:Q43”)) Is Nothing Then Exit Sub
If xfrActiveCell = “” Then xfrActiveCell = ActiveCell.Address
xfrLastCell = xfrActiveCell
xfrActiveCell = ActiveCell.Address
With Target
ActiveSheet.Cells(Range(xfrLastCell).Row, 2).FormatConditions.Delete
ActiveSheet.Cells(ActiveCell.Row, 2).FormatConditions.Add xlExpression, , “TRUE”
ActiveSheet.Cells(ActiveCell.Row, 2).FormatConditions(1).Font.Color = vbRed
ActiveSheet.Cells(ActiveCell.Row, 2).FormatConditions(1).Font.Italic = True
ActiveSheet.Cells(ActiveCell.Row, 2).FormatConditions(1).Font.Bold = True
End With
End Sub
Thanks so much for the follow-up and letting me know.
Tom.. I am still learning and your post here has helped. Thank you. Q – I am using the code below, but my problem is that it is overriding an already existing Rule I have set up. This is where if data is entered into Y2 it will turn A2 red. this works Y2-Y50 and A2-A50. As soon as I enter the code it’s as if the first Rule never existed. Any way to fix this? or is there maybe a simpler way to make it so that the active cell is just move viable to those with bad eyesight?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
‘highlighter
‘Mike H does not destroy existing formats
Cells.FormatConditions.Delete
With Target
.FormatConditions.Add Type:=xlExpression, Formula1:=”TRUE”
With .FormatConditions(1)
.Interior.ColorIndex = 8
End With
End With
End Sub
I’m not exactly sure what you’re saying, but if you only want the code to be triggered when cells in range Y2:Y50 are selected, you can insert this line of code as the first line below the Private Sub Worksheet_SelectionChange(ByVal Target As Range) event line.
If Intersect(Target, Range(“Y2:Y50”)) Is Nothing Then Exit Sub
Then also, instead of
Cells.FormatConditions.Delete
you can have
Range(“A2:A50”).FormatConditions.Delete
Sorry for my confusion. I have a conditional formatting in place to change A2 to red when data is entered into Y2. What I am trying to do is color the active cell so it can be seen easier. But when i enter the code mentioned in the first post, my conditional formatting is deleted. I tried the changes you suggested and it kept giving me errors. I hope that is easier to understand.
I don’t know what “keeps giving errors” means; the code I posted does not error unless maybe your worksheet is protected, which if that’s the case you can unprotect and reprotect the sheet in the code.
Also not clear what you want red, where exactly you want it, where you don’t want it, when you want it, and when you don’t. And if only columns A and Y are involved or should any and all worksheet cells be involved with anything you are referring to, especially when you wrote “color the active cell”.
It sounds like you need conditional formatting for the cells in column A and actual interior cell color to change for any other selected cell, or any cell in column Y, or maybe column A when you select one of those cells as you said, but I’m lost on what you need.
You helped me sometime ago. I used to write macros in Microsoft Office version 2003. I am now in my 80’s and sorta remember what to do and want to write an excel macro for office 365; I have either version 2016 or 2019 on Windows 10 PC. I want something very simple — LOVE WORKING ON THE COMPUTER YET.
I use several Excel Workbooks with several pages or tabs in it. I only need the macro to work on 3-4 pages to help find my place (worksheet may be about 80 rows and from column A – MM). This is not a business workbook, just a Home Personal workbook for keeping track of expenses, income, etc.
All workbooks are white background with black text. Once in a while I highlight a cell in order to go back easily and edit it and also if I enter a negative number, I enter it in red text with parenthesis. I would like to highlight the active cell in a light turquoise color background with black text and when I move out of the cell, it goes back to the white box / black text. Is this possible?
Lastly, about saving this macro to use in other workbooks; I have a blank sample template that I reuse every year and just rename it to start with the year, so the records are kept. Is it easier to save the macro on a sheet in each workbook or would I be better off to save it in PERSONAL WK under XLSTART folder and have it open in every workbook, which I don’t necessarily need. Can you help me with the saving and the best way to do it and name it. Thank you so much. You have helped me greatly in the past. Hope you could help me again. My memory comes and goes.
Hello Anita – –
A few questions…
(1)
Do you only want to highlight active cells on the 3-4 sheets or on any sheet?
(2)
What does “white box / black text” mean? Is the cell (I assume you mean cell when you say “box”) white because it was never altered and therefore in its default untouched condition, or is it white because you colored it white yourself by purposely formatting it? Same as why the black text is black — is it the default color or is it a custom black that you established somehow.
(3)
What steps did you take to format the negative numbers to be in red – – from the Format Cells dialog box maybe, or did you do it with conditional formatting?
(4)
Regarding what you wrote in your fourth paragraph, I avoid the Personal Macro Workbook like the plague, and so should you, as it is outdated and obsolete. You can create a template file for what you are talking about, but because you are asking my opinion, seeing as you only need to update this once a year, I’d do it the manual way, maybe as Auld Lang Syne is playing on New Years Eve, as this is a very quick save-as you are talking about.
#1) Just on 3-4 sheets; if it is easier, it can be done on all pages. I have used simple entries and conditional formatting in the past. Want the easiest way to do this.
#2) the default color – white cell box with black text. If the cell should be highlighted in yellow for easy editing location, I would like it to still be yellow after I am through entering in that cell.
#3) I just changed the text color to red if it shows a negative number. Otherwise, I just leave it as the default black text.
#4) Simplest way for saving the macro is my choice. Do I save it in the Template File on a empty sheet (page). I need help with this. And will it run when I open the workbook I am using?
Thanks so much for your help. Anxious to get this going again.
• What are the names of the 3 or 4 worksheets you only want to include.
• Regarding #4, easiest way is to put the code into a workbook, save the workbook as a macro-enabled workbook (.xlsm extension), and do a save-as for that workbook when you want to rename it for a new year.
I think I am trying to make this more complicated for you; do whatever is the easiest, please. Here are my answers to the best of my ability to help you:
I have 5 worksheets in each workbook that I will use the active cell highlighting on. Their names are: “YEAR”, “SHARED YEAR”, “MEDICAL”, MEDICAL MILEAGE”, “TAX INFO”
If you want the following information, which might make it more confusing with size of workpages, this is it. If not disregard it. On all worksheet top row(s) and column A are the heading titles. The active cells I use for numbers are adjacent to that.
So, on “YEAR” cells for numbers go from column B through O and rows start on row 4 through 57
On “SHARED YEAR” worksheet columns B through AL; 10 rows long
On “MEDICAL”, number cells start row 5 and usually goes into a total of about 3 pages long (about 177 rows)
“MEDICAL MILEAGE” do same as “MEDICAL” sheet
“TAX INFO” worksheet – number entry starts on row 6 and is 41 rows long and goes from column B through N
For saving, I totally understand how to do the save using Save As and rename. I do this all the time to have two different files more or less with the same name concept. I plan on putting the macro into the Template or Sample Workbook, whatever I called it and then when I start a new year workbook with the correct name, the macro will already be in that workbook ready to go. By the way, I assume that this Template File with the macro, is saved in my list of folders/files and not in the hidden XLStart area and Personal Workbook. Am I correct? Thanks again.
Put this in your ThisWorkbook module and save your workbook (if you have not already done so) as macro enabled. Worked fine for me when I tested it.
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)With ActiveSheet
If .Name <> "YEAR" And .Name <> "SHARED YEAR" And .Name <> "MEDICAL" And .Name <> "MEDICAL MILEAGE" And .Name <> "TAX INFO" Then Exit Sub
End With
If Target.Cells.Count > 1 Then Exit Sub
With Target
.Worksheet.Cells.FormatConditions.Delete
.FormatConditions.Add xlExpression, , "TRUE"
.FormatConditions(1).Interior.Color = vbCyan
.FormatConditions(1).Font.Color = vbBlack
End With
End Sub
Your Dummy Idiot is back again. Sorry! I located on my hard drive the folder/file with XLSTART and PERSONAL FILE for macros. I deleted both of these along with a file in excel. I opened up my “ANITA’S BLANK WORKSHEETS” file saved in Excel. I added at the end of this wookbook another page named Macros and saved the entire workbook as Anita’s Blank Worksheets.xlsm
When I use it for a new year, I will save it AS 2019 Anita’s Expenses.xlsm, so I don’t loose the original master copy.
While, I have created macros past years and they worked, I am having trouble now. I copied your codes on my blank macro work page, but apparently I didn’t start with the right line of wording as nothing works. I did click on Developer and entered a name for the macro and and saved it to be used in this workbook. I saw the tab on the left saying Module and put it there. It doesn’t run.
Can you help me once again with what I need to do to get it working? Will it just work on the pages I would use it on that I mentioned to you and not the other pages? This would be great. If so, how do I make it execute and does it automatically work when I click on a particular page?
When I close the workbook out and go back to Excel, I assume I save two times to keep everything up to date. I do not need Excel to open every time I turn on my computer, unless I will be working in Excel.
Any detailed help is appreciated. You really got a Dummy here or is it my age creeping up on me, which I don’t want to admit.
I emailed you an example workbook to show where the code goes in the ThisWorkbook module, and that the code does what you said you want it to do, only on the worksheets you specified.
Hi, Tom. Changing the background color on a selected cell works. But I realized what I really want is to have it highlight only when the Excel window is active.
I am working with an Excel spreadsheet and a C program simultaneously. I keep finding I have been typing for the last minute into the spreadsheet when I meant to be typing into the C program, and vice versa. I need some way to make it blindly obvious when I am, and when I am not, in the Excel window.
I gave my operating system windows a wide orange border when they are active. But apparently Excel overrides that.
Hello Rick – –
I understand what you are asking for and that you are also working in C. Your question is something I have not encountered for this example’s situation, but you got me thinking about this because it could relate to any app activated outside of Excel.
As you know, VBA for Excel has many built-in events such as Workbook_Activate & Deactivate, and WindowActivate/Deactivate. Because none of them will do what you need, this would be an API call situation.
If it were me, I would declare a boolean variable to return either TRUE or FALSE in conjunction with when the Excel workbook window is activated or deactivated. I would put that boolean variable in a procedure triggered by the API declarations that would monitor the Excel window being activated and deactivated.
Here are 2 links related to this that came to mind:
(1) From Chip Pearson:
http://www.cpearson.com/excel/ActivateExcelMain.aspx
(2) From Jan Karel Pieterse, a comprehensive page of API declarations including PtrSafe usage:
https://www.jkp-ads.com/articles/apideclarations.asp?AllComments=True
Thanks for bringing up this topic. Please feel free to post back with how (if either) of these API pages help. I haven’t had to write out the code so far, but after your question, I should do that at some point for myself when the same situation arises.
Hi, Tom.
Well, your giving me that information convinces me that I shouldn’t do this right now. I should just tolerate the existing behavior. I was hoping to get my project done today, and instead I have spent a lot of time trying to adjust my tools. (mea culpa)
When one of us develops the solution, I bet a lot of people will be happy. Regards
I notice that there is a “shadow” around my Excel 2016 window, that is not around other operating system windows, and is only there when the focus is on the Excel window. I wonder whether there is some way of growing that shadow, or changing its color.
Not that I’m aware of. Microsoft has been adjusting a few Excel and Windows properties lately, so you might be seeing results of that (which I do not), depending on the versions you’re using.
Never ends, does it. Always something new or different.
:^D
Tom,
I checked all of these codes. I have a spreadsheet that has tons of existing conditional formatting built in already. There are also manually colored cells. I have grouped ranges as well. Each of these codes bugs out or erases existing formats in my sheet. As soon as the code is dumped into VBA.
I need a code that highlights the column and the row that doesn’t erase existing manual coloring or the existing conditional formatting in my worksheet. I don’t know if this is possible–because I have been searching the net relentlessly in search of a VBA code that does this–including the abovementioned codes. The only code I have in the existing sheet is:
Private Sub CommandButton1_Click()
On Error GoTo booboo
unpass = InputBox(“Password:”)
For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Unprotect Password:=unpass
Next
Exit Sub
booboo: MsgBox “There is a problem – check your password, capslock, etc.”
End Sub
The main sheet stays locked with a password to prevent accidental data deletion.
When I extend out all the groups of columns the sheet is huge–E.G. Why I could really use this to see where I’m at.
Do you wish to highlight…
• only the active cell
• an entire row
• a row only within the selected cell’s current region
• an entire column
• a column only within the selected cell’s current region
I’m also looking for something that doesn’t disable the undo function. Maybe it can’t be done….:/ I’ve tried some add-ins and had to remove them because it disables undo.
With event programming like this, the undo stack is negated. There are ways to capture the changes such as cell edits and so forth, and reinstate them programmatically, but it gets messy and I would not go that route.
An entire Row and Column once you have clicked on a specific cell intersection. If this is not possible then only the entire row.
Whether or not it’s an entire row and entire column that you want, of just an entire row, makes no difference as to the complexity of a solution.
It’s always best to think about this during design time. But at this stage after the fact, if it were me and restoring the formatting is the issue, I would try copying the row and column formatting (copy pastespecial formats) to a dedicated row and column to retrieve them later, and upon selection and deselection, override (with the code you have) the current formats in favor of the selection-related formats, and then when another cell is selected, copy and pastespecial the original formatting. Kind of clunky but it’s a possible workaround.
I saw in another forum someone suggesting that the code be turned off and on when you want to use via a macro button. In this case I could understand not having undo while using the highlighting code active.
Maybe this is a possible solution?
I don’t understand how code can be turned off and on by code that is not supposed to execute. If whatever you saw in that forum is something you can try, go ahead and see if what they specifically posted works for you. After that, please post back here with the results, which I would be interested to know. Thanks.
I couldn’t figure it out either. In the end I went here:
https://www.jkp-ads.com/download.asp
and grabbed an add-in tool called “FollowCellPointer”. I ran some tests on a copy of my workbook using this add in and it does not jack with existing formatting–or copying or pasting. The downside is while the tool is active you lose undo. But you can toggle it off and undo comes back.
So far this is the best option I have encountered and I will use this.
Thanks for your help:)
Great, thanks for the update. Jan Karel is among the very best VBA and API programming experts anywhere.
There is a problem. I have a range A1:E25 color with Blue.
But after use the code no background color was saved.
What i do.
Have you tried the conditional formatting code that I posted as a reply to other comments similar to what you are asking.
How can I get this work on a protected worksheet? When I unlock my worksheet it works, when I lock it, it gives me this: “Run-time error ‘1004’: application-defined or object-defined error” and gives me the options to end or debug. – When I click debug, it takes me back to the view code screen and highlights this line of your code ” Cells.Interior.ColorIndex = 0″. When I go to close the view code screen, it tell me this command will stop the debugger (click ok or cancel). IF i click ok and return to the spreadsheet and click in another cell, it just goes through this all again. I must have the sheet protected to protect cells that have formulas in them.
See my reply with your follow-up comment.
Another thing it did when activating this code was it removed ALL of the coloring form cells that will are ‘locked’ when the spreadsheet is protected. How do I keep my sheets formatted as currently saved while still adding in the selected code.
I am trying to use the “active cell only option.
A couple things…
(1)
Are you sure that at the beginning of the code you are unprotecting the worksheet, and at the end of the code you are re-protecting it.
(2)
This example might be the one I’ve ever posted that has elicited the most comments and questions for modifications to fit various scenarios, which is great, that is where VBA really shines. Among the comments are many that involve the same kind of issue you may be referring to, which can be resolved by using conditional formatting instead of actually changing the cell’s interior color property. Take a look through the previous comments here and see if my replies that use conditional formatting in the code help to answer your situation. If not, then post back with what ranges you are working with.
Ok, reading through your post above, I was able to find the right one for saving my already color filled box. They are staying as formatted now but I am still having a problem with I protect my worksheet. The code it is wanting to debugged now is “.Worksheet.Cells.FormatConditions.Delete” When my worksheet is protected, there are cells that are locked. working with codes and this complex of formatting is not something I ever do, would easier if you had a copy of the workbook to help figure out what it is or isn’t doing? vs what I would like it to do when it is protected?
OK, you can send me the workbook phonetically to
tom
at
atlaspm
dot
com
Hi Tom,
I like the Cyan color of the box as it doesn’t confuse folks if the conditional coloring of the spreadsheet uses Yellow as an intelligent color like Red, Yellow, Green.
However, when I use Cyan for the interior color the text becomes White and is hard to read. Is there another line of code that could be inserted to ensure the text color is Black and not White?
Please advise.
Thank you Sir!
Scott
Hi Tim, please disregard previous message as I figured it out. For all others, here’s the additional line of code I inserted.
.FormatConditions(1).Font.ColorIndex = 1
For context…here’s all the code I apply to “ThisWorkbook”, where the number “1” in visual basic represents the color value of black.
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
With Target
.Worksheet.Cells.FormatConditions.Delete
.FormatConditions.Add xlExpression, , “TRUE”
.FormatConditions(1).Interior.Color = vbCyan
.FormatConditions(1).Font.ColorIndex = 1
End With
End Sub
Thanks!
Scott
That’s great, thank you for the question and follow-up solution, Scott.
Hi Tom im new on vba and i use your code for conditional formating.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
.Worksheet.Cells.FormatConditions.Delete
.FormatConditions.Add xlExpression, , “TRUE”
.FormatConditions(1).Interior.Color = vbYellow
End With
End Sub
my problem is that on columns U,V,W,X,Y,Z,AA,AB I Use coditional formating data bar which i loose when i use the code.These cells on columns have formula and are locked.The use is only for appearing more easy the results.
Can you help me with this if itsnot very difficult for you.Thanks in advance
Hello Koumentakis:
Thank you for letting me know that my blog has helped you.
Can you please explain what you mean by “I use conditional formatting data bar which i loose when i use the code.” I am not sure what you mean.
Do you want something to happen
or
to NOT happen
to cells in columns U:AB?
Hello Tom,thanks for your quick rensponce
I mean that i have use from ribon of excel (i use excel 2019) the button of conditional formating
and from there the choise data bar and after the color.This i do for visual reasons to be more easy to see how much is per day the volume of a tank.
So the problem is after the use of code it keep all colors on sheet but dissapear the bars in mentioned columns.
The sheet is for daily volume update of tanks for ship.
Thanks for your help
I want to update Tom, i Used also this on workbook
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 0
Target.Interior.Color = vbCyan
Application.ScreenUpdating = True
End Sub
With this code i can see the bars but i canot change the background color of cell.After use its coming the color of the table which i use and i can change only color of table.
Can you give me a solution with a code to can maintain and the color of cells and to be visible the formating with bars on columns which i have.Thanks again.Im looking forward for your news
Can you please clarify and confirm that cells in column U:AB
• do have conditional formatting applied to them
and
• you want to keep that conditional formatting in those columns, and only those columns, intact and untouched by the code.
Hi Tom,thanks for your efford to help me. I succeed by creating a new custom table and using the code.So i can keep and the colors and the coditional formatting with bars.Thanks for the code .
Thank you for the update.
Hi Tom, you and you’re skills are very helpful. Can you help add another properties on this code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
.Worksheet.Cells.FormatConditions.Delete
.FormatConditions.Add xlExpression, , “TRUE”
.FormatConditions(1).Interior.Color = vbYellow
End With
End Sub
Your code works fine. What I wanted to add is that when I have a blank cell, the color should change, it could either be no color, white or any other color as long as it is different to my pre-colored cells.
Thank you Tom.
See if this version comes close to what you want:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)With Target
.Worksheet.Cells.FormatConditions.Delete
.FormatConditions.Add xlExpression, , "TRUE"
If Len(.value) = 0 Then
.FormatConditions(1).Interior.Color = vbWhite
else
.FormatConditions(1).Interior.Color = vbYellow
End If
End With
End Sub
It works Tom, but what I really wanted is when the cell is blank even without selecting it turns to no color or white, and when I input the data or value on the cell it return to its original color, something similar to =ISBLANK function. I tried to use the =ISBLANK function after I used your code but it doesn’t seems to work with it.
Is this just for one cell or for many cells?
This could be part of a WorksheetChange event, or you could maybe have that condition be a part of the SelectionChange event except that that was what I posted so there must be some aspect of your worksheet or other condition that I’m not comprehending from your question.
That is for the worksheet, well your code works, it just that I need the blank cell color to change whenever no value, even without selecting it, and revert back to its pre-colored cell when there is value. What I notice in the code is that I need to select the blank cell to see the change in color. Thanks Tom. Sorry to confuse you.
Tom! Do you realize how many people are benefiting from your kind knowledge-sharing?!?! We’re all indebted to you. This little gem:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
.Worksheet.Cells.FormatConditions.Delete
.FormatConditions.Add xlExpression, , “TRUE”
.FormatConditions(1).Interior.Color = vbYellow
End With
End Sub
with “straight Quotes” instead of “slantie quotes” around the word “TRUE” is a LIFESAVER!
Wow, thank you so much for your message, Beth!
I discovered this bug-fix a few years ago when I was creating my blog examples by pasting my examples code directly from the VBE (where the macros worked fine) into my back-end html page, but people were telling me they couldn’t get the code to work that they understandably copied off my actual public web page. I couldn’t understand why until I copied back my own code from the actual web page into the VBE and saw right away that the double quotes were, unbeknownst to me, converted from my original ascii character 34 (the “straight quotes”) to 147 and 148 (the “slantie quotes” as you called them, ha ha).
If in cells A1:A3 respectively you were to enter…
=CHAR(34)
=CHAR(147)
=CHAR(148)
…you’d see those 3 characters where cell A1 looks like what you’d want but cells A2 and A3 hold those troublemaker wannabe quotes.
Thanks again for your comment!!
“troublemaker wannabe quotes”. *snort!* You’re a scream.
So, please keep on patiently guiding us all with our muggle questions, use your powers for good, and enjoy your 4th!
…and may the 4th also be with you!
Hi,
can you help to give the vba codes for highlighting teh 1st cell of teh row and colum with reference to the active cell and not the highlighting the entire row and column
Private Sub Worksheet_SelectionChange(ByVal Target As Range)Cells.Interior.ColorIndex = 0
Cells(Target.Row, 1).Interior.Color = vbCyan
Cells(1, Target.Column).Interior.Color = vbCyan
End Sub
Hello Tom
Nice code and thanks.
I have a question.
I just want to hightlight the row, not collum.
AND..
My spreadsheet has a “header” where row 1 and 2 is black background and white text.
When I used the code around in the sheet, row 1 and 2 lost the black background.
Any way around this?
Regards
Jostein
Hello Jostein – –
Yes, there is a way around this. Can you first please clarify if there is other formatting (other colored cells) belonging to rows other than row 1 and row 2, and if there is conditional formatting of any cells in rows other than row 1 and row 2. There are a couple different ways to do what you want, I just need to understand which way would work better, depending on your existing (or not) formats on that sheet.
Tom,
Thanks for wanting to help me.
Yes… I forgot collum F is also black. But, that is not so important. Its not a must.
I dont use conditional formatting that I have implemented.
I found some info on this, but did not get that to work. Only your codes and someone simelar in develpor.
This is a spreadsheet where row 1 and 2 is freezed, acting like a banner.
Row two also have filter activated so I can choose content that I would like to see.
Other rows have multiply formulas in different versions. % etc.. calculations.
Regards
Jostein
Forgot, Colum G and H also have radom coloring… 🙂
Right-click on your sheet tab, left click on View Code, and paste this into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)With Target
.Worksheet.Cells.FormatConditions.Delete
If .Row > 2 Then
Rows(.Row).FormatConditions.Add xlExpression, , "TRUE"
Rows(.Row).FormatConditions(1).Interior.Color = vbYellow
End If
End With
End Sub
Tried it, but no action. Just to check I did not do anything wrong, I tested the middle code in the start of this page, and that works. But then with colum and colors at row 1 2 go away…
Then the colors of your cells in row 1 & 2 are conditional formatting which I asked you about and you sad they were not.
Thank you sir! This was a big help to my team. Please keep up the great posts!
Thank you, Bob!!
The VBA formula that I used is listed below. It is working but not automatically. When I change cell selection, I have to hit F9 for it to move the highlighting to the new row/column. Other websites suggested that I enter Conditional Formatting from the Home Ribbon. I’ve done it with and without that and all that seems to do is change the color to the one I selected in the conditional formatting. Any suggestions?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 0
With Target
.EntireRow.Interior.ColorIndex = 8
.EntireColumn.Interior.ColorIndex = 8
End With
Application.ScreenUpdating = True
End Sub
Hello Joy – –
Well, let’s see…
The code works for me and I’ve not heard anyone recount the same experience you are getting, so I wonder, considering your mention of the F9 key, if your Excel system is set to manual calculation mode. The F9 key recalculates all formulas in the active workbook.
Just for starters, from your worksheet, press the
Alt+F11keys to go into your visual basic editor. In the VBE, hit theCtrl+Gkeys which will put you into the Immediate wndow.Next, type or paste this into the Immediate window and hit the
Enterkey.Application.Calculation = xlCalculationAutomaticFinally, press the
Alt+Qkeys to close the VBE and get back to your worksheet.This puts your Excel app into Automatic calculation mode.
Now try selecting a cell or two and see if the code gets triggered, which it should.
Now, if this does do the trick, there might be a very good reason why your workbook was set to Manual calculation mode in the first place. In any case, see if this helps and post back with what happens after you try it.
Thank you so much! Thi is so helpful.
Re: Saving add in and file
I have windows 10 64 bit and Microsoft 365. Also have 2 computers, so I use Dropbox and local computer to save files, so they will be up to date on both computers.
I used your active cell add in and it worked great. Then problem– next time I opened Excel it did not work in file I saved it in and any other workbook. It was saved as add in *.xlam in C:\Users\Anita\AppData\Roaming\Microsoft\Addins\Active Cell.xlam (it is checked as active in the list of add ins.)
The workbook that I opened to write or copy the VBA codes in is also saved in my folder -Dropbox\Anita’s Files\Microsoft Office Files\Year Expenses\name of file.xlsx
What am I doing wrong? Also, if I would want to add another add in to use, do I put it in the same file as the active cell or use another file? I am entering the code in ThisWorkbook, so that I will or should be able to use it in any file. The code is in the VBA Project. Does it need to be in that and the Workbook name Project on the ThisWorkbook page? Hope you can help me. Very frustrating!
Hello Anita – –
There are a lot of event examples in the comments plus my original 3 examples in the post itself. I wonder which you are using and if you maybe are using a Worksheet level Change event for the add-in that is only mean for one worksheet when you should be using the Workbook level event to handle this for any worksheet in the workbook. If you create a single add-in file properly, with just one of those events in it, it should work for you. Not seeing what you did and how you did it, my first suspicion is that you put event code that conflicts with or is ambiguous towards other event codes. I wish I could offer more suggestions but I cannot re-produce the behavior you are seeing.
Thanks for the reply. I think you were right; I used Sheet instead of Workbook. It is now working with some adjusting. I’ve saved it as an add-in, but it does not start working in other workbooks that I open. The code is in Active Cell add-in. I have to go into VBA and copy and paste the code into the workbook sheet (module) and then it works. What do I need to do to make this available in any file I have that I would want to use the Active Cell code in?
I do not understand what this means: “The code is in Active Cell add-in”. You do not need to refer to an active cell because the Target keyword that comes with these procedures is the cell that is the active or selected cell.
I’m needing help on 2 Excel spreadsheet issues that I hope you can guide me through.
First, I’m wanting to color categorize rows in 2 columns using the color I select from my color legend. So, …
I have a column of cells that I have filled with the standard Excel colors from the color palette and I used =COLOR in the adjacent column to return Excel’s number for that color. A color legend, if you will. A SUMIF function will add up the matching colored cells in the first 2 columns.
I’m wanting to 1.) select cells in 2 adjacent columns then 2.) drag and fill the selected cells with the color I select when I click on one of my legend cells. The rows I’m needing to fill are not consistently adjacent to each other as I may skip several rows before continuing to fill other cells.
I’m only using 46 of the colors that Excel recognizes so my legend is 46 rows deep. The columns with the rows I’m wanting to fill are several hundred rows deep.
My present approach is to click on 1) a colored cell in my legend, 2) select the cell Fill option, and if the color I selected is not immediately shown in the first window I then 3) select “More Colors” from the menu so I can see which color I’ve selected from my legend. 4) I then click “OK” and my selection is shown in Fill icon. 5) I then select the cells I want to fill with that color and 6) while they’re bordered select the color from Fill icon to fill the cells. This works fine as I then have a SUMIF function to add my selected cells. BUT, 6 steps takes too long!
Is there a way to reduce these 6 steps above down my preferred 2 steps? Any variation thereof will also work. I’m wanting to limit clicks and reduce user time. I’m very open to suggestions!
Hi Tom
would you be able to help me, i am using your code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 0
Target.Interior.Color = vbCyan
Application.ScreenUpdating = True
End Sub
and it is working perfectly, however, the problem i have is when i copy the “highlighted/selected” cells the cyan formatting goes with it
how can i not copy the colour with it – but i do want the normal formatting like spacings/fonts/colums etc
A couple things I wonder about are, first, when you copy the cells, are you intending for those cells to also be highlighted in the place where you paste them, as they get selected, just like how they were subjected to the color change from where you copied them.
Second, are you copying them manually of with programming code. If it is the latter method, a simple line of code to have all pasted cells be of no interior color (the color index for no color is -4142). If it is a copy and paste manually, it would only be 1 cell we are talking about, so you could just manually change its Fill color to “No Color” in the Format Cells dialog box.
The other option is to have conditional formatting be the trigger for the cells’ color. Instead of the SelectionChange event you are using, you could use this instead.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)With Target
.Worksheet.Cells.FormatConditions.Delete
.FormatConditions.Add xlExpression, , "TRUE"
.FormatConditions(1).Interior.Color = vbCyan
End With
End Sub
Then when you copy the range to wherever you are copying it, you can eliminate all conditional formatting on that destination sheet (where the pasted range has been copied) for the entire sheet or just those selected cells by going to your Home tab on the Ribbon, then go to Conditional Formatting, mouse over Clear Rules, and select either Clear Rules From Selected Cells or Clear Rules From Entire Sheet. All your other formatting such as you mentioned will stay intact.
Thanks Tom
to answer your questions – when i copy(manually) the selected cells(a large selection of cells, columns and rows) to the other sheet – the “cyan” colour copies to the new sheet as well.
It does not work to change the “fill” – as it does not see the “cyan” as a fill.
The clearing of the conditional formatting does work though, Thanks very much
Thanks for the follow up.
Hello Tom,
could you please help me out?
1. I need to highlight the entire row and column, but I want the original formatting of the cells to be retained when I click on another cell again.
2. Is there a way to specify that if I click e.g. cell A3, that cell A3 is marked yellow, the complete row and complete column is marked cyan and additionally the cell at 5 cells further to the right of A3, cell A8 is marked red? It would be nice if here the formatting of the cells be retained, when I click on another cell.
Thank you very much for your feedback.
This will do what you want. It goes into the worksheet module, and be sure to first delete any SelectionChange event that might already be in that module.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 0
With Target
.EntireColumn.Interior.Color = vbCyan
.EntireRow.Interior.Color = vbCyan
End With
With Target
.Worksheet.Cells.FormatConditions.Delete
.FormatConditions.Add xlExpression, , "TRUE"
.FormatConditions(1).Interior.Color = vbYellow
If .Column <= 16379 Then With .Offset(0, 5) .FormatConditions.Add xlExpression, , "TRUE" .FormatConditions(1).Interior.Color = vbRed End With End if End With End Sub
Please help with the End with end if end with end sub. is goes red and not allowing me to run
Hey Tom,
thanks for this code!!!
I have existing conditional formatting in my cells that use icons based on the value. Example, if the value is less than 0, it shows the red down arrow.
When I use your code, it deletes all my conditional formatting that I have setup with icons.
is there a way to retain all my existing conditional formatting?
Hi John, thanks. Just so I understand the nature of your project, do you need conditional formatting for the cell colors. If not (maybe you already knew this but I’m just checking), and you have no other cells colored otherwise, you can use the option for changing the cell’s actual interior color property, which would leave CF alone and your arrows would not be affected.
Hello Tom,
I do like this code, thank you. I have existing conditional formatting that alternates coloring every other row. I was using a Target.Name = “mySelection” with Worksheet_SelectionChange(ByVal Target As Range), along with conditional formatting rule of =AND(MOD(ROW(),2)=0, ROW(A1)ROW(mySelection)). The problem with this is you will crash Excel if someone decided to select an entire column! I thought about hiding the headers, but this is limited to someone not going in and just unhiding them and exploring. So, long story short, can existing conditional formatting be retained but be overridden to apply the highlighting using your code? I don’t want to delete conditional formatting on the sheet, but override it. Thank you.
Please define ” will crash Excel if someone decided to select an entire column”. What exactly happens.
Works like a charm… I have been looking for something like this. Deleted the column part of the code to just highlight the line and it works perfectly. Many thanks Tom.
Thanks, Angel!
Hi Tom,
This is awesome. Thank you!!!
One question, please. I see that we can change the codes to work within the Workbook instead of the worksheet. Can you take it one step further, to have it work globally? I mean to work on all open workbooks? My guess is to copy it into the Personal.XLSB module. But I don’t know enough VBA to modify the codes. 🙂
Again, thank you.
Hello, Victor, thanks for the kind comment.
To answer your question, the answer is yes, but I would not bother with the Personal Macro Workbook or the Personal anything. Too many minefields and Microsoft is moving away from the Personal thing anyway.
If it were me, I would make it an add-in (.xlam extension) and just enable the add-in to the workbooks you want it to apply to. Add-ins are not as complicated as some people think. Maybe you already know that but in case you are not sure how to do it, there are plenty of examples on the web. If you get stuck with anything specific as (if) you go that route, feel free to post back.
Thank you very much. All these modern colors schemes are great for young eyes. I have been just about going blind trying to find the active cell while doing an annual budget. This is the first year I have had a problem. What a relief with such a simple fix. Also thanks to you, I learned something new about Excel’s structure itself.
I am very grateful for your help.
What a nice comment. Thank you, Rosemarie!
thank you so much,
> how we can make it in add-ins to work with all excel workbook
Search for keywords
Create
Excel
Addin
and plenty of sites show how to do that.
Thank you
thank you, tom! great…!
please note that the control +z function is not working after the application of your code. kindly help us to resolve the issue
There is no issue to resolve. That is the nature of event programming. The Undo stack is eliminated at each event execution, in this case each time a cell is selected.
Hey Tom,
How are you doing? I am having an issue with the below code. The excel file I am using needs to be protected from others making changes since they should only be using it to select links in unlocked cells. The code works fine up until I protect the sheet. Once protected, when I go to select a cell I get the error “Run-time error ‘1004’: Application-defined or object-defined error”. When I run the debug, it highlights “.Worksheet.Cells.FormatConditions.Delete” as the issue. Any chance you can help me out? I don’t have any conditional formatting. The only thing I have going on with the sheet are hyperlinks to unlocked cells.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
.Worksheet.Cells.FormatConditions.Delete
.FormatConditions.Add xlExpression, , “TRUE”
.FormatConditions(1).Interior.Color = vbCyan
End With
End Sub
If you are using this code, then yes you would have conditional formatting on the worksheet, that being in the selected cell colored cyan at that moment. Just as a quick workaround, I think you can wrap the code inside protect and unprotect code lines as such:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Unprotect (“YourPassword”)
With Target
.Worksheet.Cells.FormatConditions.Delete
.FormatConditions.Add xlExpression, , “TRUE”
.FormatConditions(1).Interior.Color = vbCyan
End With
ActiveSheet.Protect (“YourPassword”)
End Sub
Try that and see if it helps solve the issue.
It is essentially protecting my sheet every time I click anywhere and making the linked cells say “Cannot open the specified file”. I can even unprotect the sheet by simply clicking “Unprotect Sheet”. No password prompt or anything. My cell selection is also not highlighting anymore as a fill but doing the standard dark green box outline.
By the way I appreciate the quick reply.
Send me your workbook or a reasonable facsimile of it, with details about what should happen or not happen when this cell or that cell gets selected, or this link or that link gets clicked. I’m lost on what is happening and the why of it. This sounds like a solvable issue.
I’m in tears.
I just spent 2 hours trying to format this in excel on my MAC and kept being denied VBA access due to some bug. EVERY TIME I thought I was close, the author would be like “now it’s time for some very easy VBA” — and this is what I get — some function denial die to a variable.
THEN I FOUND THIS.
OMG OMG OMG OMG
I am in tears. Not only did it EASILY work, but I could quickly figure out how to eliminate the column option.
People like you are the true heros of the world.
If you ever have a chance, would you email me about a very frustrating question about adjusting images. I still haven’t figure out that fix.
Thanks for this tutorial!!!!
Wow, what a reply. Thank you for letting me know that what I posted worked for you.
Regarding your issue of adjusting images, that can encompass a lot of things, depending on what kind of image it is, why you are adjusting it, if it is an adjustment for an Excel worksheet, or if you need to email it or import it or export it or use it in an ActiveX image control.
May I suggest an excellent Excel questions forum that I also contribute to when I get the chance. It is at https://www.mrexcel.com/board/forums/excel-questions.10/ where Excel experts monitor it around the clock. It’s free and no spam, registration is easy.
Why does using the code take away the ability of the undo button? I pasted the first code and it worked for what I wanted, but when I typed something and went to undo it, the undo button was grayed out.
This is an unavoidable side effect of worksheet and workbook level events. The Undo stack is erased.
There are a couple of creative tactics that can be employed, depending on the level of one’s programming savviness. One tactic is to build a utility that keeps a record (basically saves as the workbook) before executing the event, so the previous workbook status can be restored. I have done that for a few clients and frankly, it is more trouble than it is worth.
I say this with all respect, your question suggests that you were unaware of the event execution consequence of negating the undo stack. Most users who ask for it in a project understand that this is a trade-off for the benefit of what the event code does.
Thank you. I am not too familiar with using VBA, so when you say I seemed unaware, I definitely was. I am trying to assist my supervisor with a project, and have simply been looking at ways to help her.
Now another question I have is can that first code be used on a protected worksheet? When I execute it, a run time error message pops up. This may be another simple answer that people familiar with VBA know, but again I am not savvy with it.
Definitely accomplish-able.
All you need to do is sandwich the code to be executed between Unprotect and Protect lines of code, for example:
Activesheet.Unprotect (“Password”)
Code is here
Activesheet.Protect (“Password”)
If there is no password involved with the protection, this will do:
Activesheet.Unprotect
Code is here
Activesheet.Protect
Thank you so much! That worked perfectly. Almost all her sheets are protected, so this is wonderful.
Hi Tom
If I have to highlight the active cell’s row and column I could find any solution. However, is there a way to do the same on a protected worksheet for which I do not know the password?
Thanks, appreciate your efforts.
If the sheet is password protected and the cells are locked that you want to manipulate, then you are out of luck unless you get the password from whoever knows it, or you do some surreptitious tactics that I do not publicly post.
The thing is, the worksheet is protected for a reason, whatever that reason may be, and the person who passworded it wanted the sheet to not be changed. That wish needs to be respected. The other alternative is to re-create the workbook yourself and do what you want to with your duplicated workbook.
Hi there,
I just tried this out as my very first foray into editing code for an excel sheet. It works great, however, I can’t get rid of it. I figured I could just open up the code again & wipe it clean, but the effects of the code never leave. How do I turn this off now? What obvious thing am I missing?
When I say, I can’t get rid of it, is that the highlighted cells are still cyan, & all the rest of the cells are now un-highlighted altogether. I lost my worksheets’ colour scheme 🙁
Is there a way to get that back aside from loading a backed up excel file?
The code I posted in the original example, which I am guessing is the same code you tried, assumes that the only color you want on the spreadsheet is for the cell(s) you selected.
The short answer to your question about needing to reinstate the pre-existing cell colors, the answer is yes.
The longer answer is, to avoid this from happening again, take a look through the many comments I received under this example and many of them show my reply for using Conditional Formatting to color the active cell, which does not affect any other cells that are colored not with Conditional Formatting, but by actually changing the cell’s interior color property.
If you do not have any cells that are conditionally formatted already, then this would be the option to use. That way, when you deselect a cell that was already colored, that original color will be untouched.
The comments and my replies to this thread show just about any scenario someone could want. But post back if you need more assistance, and what you are trying to do.
If you know Nevin and Russell, please tell them that Tom says hello. If you don’t know them, your name and website domain is a coincidence for the ages.
Thank you so much for all the useful info!
Nevin & Russel aren’t fellows I know, but Star Building (& Qualico) are pretty big companies now 🙂
Hi Tom!
I have been using the code you posted to temporarily change the color of the active cell. I usually select the entire column, Command+f, and type the name of company I’m searching for under that column. Excel usually grays everything and highlights the search in white. However, there’s usually a lot of color-coding which makes the cell hard to stand out. Example I select the entire A column, command+f, type “Honda”, it highlights the result but hard to spot because that cell is blue. Is there a code to help spot the result better? The code you posted only works if I dont select the column, otherwise it highlights the entire column and defeats the purpose of easy spotting. Thanks!
I wonder why my message is not being posted here.
but tom.
please help me.
i know its a lot to ask but youve already done it many times but no one asked this:
can you please help with highlighting both column and row and also the cell when you select it.
and all this while not changing the current FILL or Conditional formating colors on worksheet?
it would single handedly save my life here.
Please if possible write the whole code at once, as adding a code to another is not very easy.
i have the code for changing row and column of active cell
i have change color of only active cell
but i do not have both together WHILE keeping whatever current Fills or Contionional formatings untouched.
i thank you a thousand times.
I showed in a previous comment (from someone else’s question, not yours) how to do this for the active cell and with the column and row. Search the comments here for that.
Regarding the existing Conditional Formatting that is in place with other cells, that would need to be re-instated programmatically, and could be quite complicated because of the formatting different variations that are potentially present in all different cells along the row or column. It would be easier to know what CF you have where, so that can be re-instated that way rather than at each cell selection keeping a programmed record of all those cells.
thanks for quick response.
i have used the one u mentioned.
but that one only colors row and column and not the cell (3 colorations)
and the only one you have which does not change the current color fill is the one that only colors the active cell and not the rows or columns.,
i tried so much to combine them but were not successfull.
when i see how many of these questions you have answered already i feel shy to ask again.
but in case you had time, id appreciate ur help on this.
OK, just to start at a point that is understood by both of us, open a new fresh workbook to test this code and see if it handles the immediate need of highlighting the active row and column in color cyan, and the active (target) cell red.
Install it as usual in the worksheet module, then click a few cells on that worksheet to see if at least this issue is what you want, not caring for now about other cells with conditional formatting on your real worksheet.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 0
With Target
.EntireColumn.Interior.Color = vbCyan
.EntireRow.Interior.Color = vbCyan
End With
With Target
.Worksheet.Cells.FormatConditions.Delete
.FormatConditions.Add xlExpression, , "TRUE"
.FormatConditions(1).Interior.Color = vbRed
End With
End Sub
Yes- is is working perfectly with row and column and active cell colored.
but i lost all the other colors i has on my sheet 🙁
and that is what i actually truely need as we have a lot of color coding.
i used FILL to color but also conditional formatting in lesser instances.,
is there a way to add the option that each time color formatting goes back to normal and im not loosing all those colors?
I am aware of that, which was why I said to test the code on a new worksheet having nothing to do with the actual worksheet of interest.
That brings us to what I said in a previous comment, so now that we have passed the first test, the next question to determine is if those colors you say are currently present in the worksheet cells are either:
(1) only from Conditional Formatting.
(2) only from changing the actual interior format color property of the cells.
(3) a combination of (1) and (2).
Its amaxzing how fast you respond Tom! i appreciate it.
Well, I did as you said.
The issue is that I have a current spreadsheet which is very big and is worth years of data and many conditional formatting on some columns and also some regular fill colors on another cells.
for example we have a drop down that uses conditional formatting for color.
we also keep coloring some names with regular fill so others can do follow up on them.
i hope that i can clearly exhibit here what my situation is.
i can not use this code as all the colors else where will be wiped away.
— please tell me that there is a way to do what we did without clearing all other colors.
i dont mind if colors go away when i click on them, but when i click elsewhere i like them to be untouched.
thank you Tom for what you do.
Well, first, an editorial comment not to be fault-pointing but as a matter of Excel worksheet design practice, it sounds like you have a lot of colors going on, and at that some with CF and others physically formatted. As long as you and everyone using the workbook understand the colors and why they are so numerous and prevalent, and what they all mean, then that is a positive aspect but in my opinion it brings other issues and this is one of them. But I would not design a worksheet the way yours was. I am not saying you designed it, maybe you inherited it and/or the people who use the workbook clamored loud and long for a kaleidoscope of colors, but nonetheless it’s an ill-advised design.
If all you wanted was for the active cell to be formatted upon selection, and not its row and column cells, that would be easier.
But if you want the respective row and column cells to also be formatted upon a cell’s selection, the good news is that I can say, in answer to your plea, that yes, there is a way to do that without clearing the other colors.
The other news (notice I am not saying bad news or good news, just other news) is that it would take a level or coding that, in my opinion having done this for 25 years, rises to the level of a paid task for a developer to do it. I almost never say this in my social media and blog postings, but every so often I am asked about what turns out to be enough of a more-involved-than-usual request that is too specialized in this blog context.
I am not pining for money here; I never do that with my blog and social media answers. Yes I could do what you ask because as I say it is possible, but I have a major backlog of other contracted clients for whose development projects I am already committed. I suggest you post your task on one of the Excel groups that are monitored by people looking for paid tasks to come their way. In your case, the issue is (hint, this is what scope you would tell a competent developer, key word competent) to programmatically take into account (behind the scenes, and instantly because it’s a VBA selection event) all the formatted interior colors and Conditional Formatting rules, and their colors, for the entire row and columns’ individual cells, and of course for the target cell being selected. Those settings, which can be in the thousands, would be stored, overridden, then re-instated when another cell gets selected. And keep in mind the situation when the workbook gets closed and reopened, the formatting in all those row, column, and target cells would need to be stored for retrieval and re-instatement when the workbook is opened again and another cell gets selected.
So that’s my opinion. Sincere best of luck with it. Remember, I said COMPETENT VBA developer. A lot of people say they are but very few of them really are. Caveat emptor.
i completely understand your point and should say that maybe it was not the right thing to ask here.
it seems i was not aware of the complexity of this issue.
as i thought if keeping the colors of other cells while highlighting a an active cell was an easy code(as you posted that here on a comment) i then thought adding a high light for row and/Or a column would also follow the same rationale and you just add 2 extra lines to your code.
so now i know keeping the colors is not an easy thing to do if you want to highlight rows and columns.
i guess i am way too naive here.
i do appreciate all the very informative notes you put there.
No worries. Thanks for the follow-up.
id like to share this code with you which highlights a ROW for me without changing any other colors.
i had to add a conditional formatting formula for the color though.
can you add a column to this formula?
Here is the formula :
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ThisWorkbook.Names(“HighlightRow”)
.Name = “HighlightRow”
.RefersToR1C1 = “=” & ActiveCell.Row
End With
End Sub
Hi Tom, I think I’ve read through every single comment and am still struggling. For my database specifically, I want it to highlight row and column without losing original formatting. The following code worked well for highlighting just the active cell without losing the formatting but for the life of me I can’t find one that does column and row and still leaves everything back to the original format.
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
With Target
.Worksheet.Cells.FormatConditions.Delete
.FormatConditions.Add xlExpression, , “TRUE”
.FormatConditions(1).Interior.Color = vbYellow
End With
End Sub
Can you clarify how the existing original formatting got there in the first place. Are the cells originally formatted…
• with Conditional Formatting,
or
• were the cells originally formatted by (for example) selecting a cell or range of cells, right-clicking that selected cell or range of cells, and then you selected Format Cells from that right-click menu and then you selected a color for those cells to be, and you clicked OK.
Hi Tom
First of all – thank you for all the great support you are giving us! Muchos gracias 🙂
I’m looking for a solution regarding cell/row/column highlighting, stored in PERSONAL.XLSB workbook, working for each workbook I open. It should leave ANY original cell formatting, regardless how it was created (with conditional formatting or just by coloring cell “by hand”, or any other possible method). Which version of your code works like that?
I’ll be grateful for your help
Lucas
Thanks for the thank you.
“Which version of your code works like that?”
None of them.
If I were you, I would use an add-in. The Personal workbook is an outdated method and I never recommend using it.