Tom’s Tutorials for Excel: Color the active cell, row, or column

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

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
235 comments on “Tom’s Tutorials for Excel: Color the active cell, row, or column
  1. Stacey says:

    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.

  2. Berry says:

    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

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

  4. SHOMI says:

    THIS DOESN’T WORK IN EXCEL 2013 :((( ANY SOLUTIONS???

    • Tom Urtis says:

      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.

  5. berta says:

    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?

    • Tom Urtis says:

      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.

      • berta says:

        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.

        • Tom Urtis says:

          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

  6. Jen says:

    Thank you Thank you! This had made this entire project much easier!!!

  7. Sulma says:

    Thank you!!! This is great!

  8. Babak Nehoray says:

    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.

    • Tom Urtis says:

      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/

  9. EDWIN M says:

    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.

  10. Marty says:

    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.

    • Tom Urtis says:

      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

      • Kyle says:

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

      • Kate G says:

        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

        • Tom Urtis says:

          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.

          • Milad Mehrbod says:

            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.

      • Rlee says:

        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

  11. Dave says:

    How can I apply this to all existing and future worksheets/files that I open

    • Tom Urtis says:

      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.

  12. Dave says:

    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

  13. Corwyn says:

    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

  14. Corwyn says:

    PS I forgot to add – yes I changed work sheet to work book

    • Tom Urtis says:

      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

      • Corwyn says:

        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.

        • Tom Urtis says:

          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

          • mr bob dobolina says:

            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.

            • Tom Urtis says:

              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

  15. Corwyn says:

    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!

  16. Corwyn says:

    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.

  17. shawn says:

    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

  18. Tobin says:

    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

  19. Dawn says:

    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

    • Tom Urtis says:

      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.

  20. Rob says:

    Is there anyway to achieve this VBA function not lose the UNDO function in Excel?

  21. F. v. Heel says:

    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

  22. Erin says:

    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.

    • Tom Urtis says:

      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.

  23. Leslie Eby says:

    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

    • Tom Urtis says:

      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.

      • Leslie Eby says:

        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.

        • Tom Urtis says:

          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

          • Leslie Eby says:

            It worked!! Thanks for your help, my team will appreciate it.

          • Leslie Eby says:

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

            • Tom Urtis says:

              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.

          • Milad Mehrbod says:

            Beautiful Code-
            Can you add Rows and Columns to this one too?

            thanks,

          • Marvelous Marvin Talamantes says:

            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

          • Marvelous Marvin Talamantes says:

            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

  24. relio says:

    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

  25. relio says:

    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

    • Tom Urtis says:

      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?

  26. relio says:

    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

  27. Brian says:

    Works great, thanks for your help

  28. Tyler says:

    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

    • Tom Urtis says:

      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.

  29. Rosie T. says:

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

    • Tom Urtis says:

      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.

  30. Arturo Leon says:

    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!

    • Tom Urtis says:

      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.

  31. Vaseem Baig says:

    Thank You Tom!

    Solid Code, just double click on your worksheet in VBA Editor then Paste.
    You’re good to go!

  32. Kevin W says:

    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

  33. Excelnewbie says:

    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?

    • Tom Urtis says:

      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.

  34. Leo says:

    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?

  35. Tom Urtis says:

    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

  36. SNCTRC says:

    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?

    • Tom Urtis says:

      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.

  37. Dave Atkinson says:

    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

    • Tom Urtis says:

      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.

  38. Dave Atkinson says:

    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

    • Tom Urtis says:

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

  39. Dave Atkinson says:

    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.

    • Tom Urtis says:

      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.

  40. Dave Atkinson says:

    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

    • Tom Urtis says:

      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.

      • Dave Atkinson says:

        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

        • Tom Urtis says:

          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

  41. Dave Atkinson says:

    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

  42. Francis says:

    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

    • Tom Urtis says:

      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.

      • Francis says:

        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.

        • Francis says:

          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

  43. APetersen says:

    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

    • Tom Urtis says:

      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

      • APetersen says:

        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.

        • Tom Urtis says:

          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.

  44. Anita Sievers says:

    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.

    • Tom Urtis says:

      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.

  45. Anita Sievers says:

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

    • Tom Urtis says:

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

  46. Anita Sievers says:

    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.

    • Tom Urtis says:

      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

  47. Anita Sievers says:

    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.

    • Tom Urtis says:

      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.

  48. Rick says:

    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.

    • Tom Urtis says:

      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.

  49. Rick says:

    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

  50. Rick says:

    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.

    • Tom Urtis says:

      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.

  51. Jared says:

    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.

    • Tom Urtis says:

      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

  52. Jared says:

    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.

    • Tom Urtis says:

      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.

  53. Jared says:

    An entire Row and Column once you have clicked on a specific cell intersection. If this is not possible then only the entire row.

    • Tom Urtis says:

      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.

  54. Jared says:

    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?

    • Tom Urtis says:

      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.

  55. Jared says:

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

  56. Biplab Das says:

    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.

  57. Amanda says:

    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.

  58. Amanda says:

    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.

    • Tom Urtis says:

      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.

  59. Amanda says:

    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?

  60. Scott Davidson says:

    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

    • Scott Davidson says:

      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

  61. Koumentakis Mixalis says:

    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

    • Tom Urtis says:

      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?

  62. Koumentakis Mixalis says:

    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

  63. Koumentakis Mixalis says:

    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

    • Tom Urtis says:

      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.

  64. Koumentakis Mixalis says:

    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 .

  65. joseph says:

    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.

    • Tom Urtis says:

      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

      • joseph says:

        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.

        • Tom Urtis says:

          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.

          • joseph says:

            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.

  66. Beth says:

    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!

    • Tom Urtis says:

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

    • Gopakumar says:

      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

      • Tom Urtis says:

        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

  67. Jostein Berntsen says:

    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

    • Tom Urtis says:

      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.

  68. Jostein Berntsen says:

    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

    • Jostein Berntsen says:

      Forgot, Colum G and H also have radom coloring… 🙂

      • Tom Urtis says:

        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

  69. Jostein Berntsen says:

    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…

  70. bob jones says:

    Thank you sir! This was a big help to my team. Please keep up the great posts!

  71. Joy says:

    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

    • Tom Urtis says:

      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+F11 keys to go into your visual basic editor. In the VBE, hit the Ctrl+G keys which will put you into the Immediate wndow.

      Next, type or paste this into the Immediate window and hit the Enter key.
      Application.Calculation = xlCalculationAutomatic

      Finally, press the Alt+Q keys 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.

  72. NITS says:

    Thank you so much! Thi is so helpful.

  73. Anita says:

    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!

    • Tom Urtis says:

      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.

  74. Anita says:

    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?

    • Tom Urtis says:

      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.

  75. Tim says:

    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!

  76. julie says:

    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

    • Tom Urtis says:

      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.

  77. Julie says:

    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

  78. Mustafa says:

    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.

    • Tom Urtis says:

      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

  79. John says:

    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?

    • Tom Urtis says:

      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.

  80. Derek says:

    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.

  81. Angel says:

    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.

  82. Victor Moran says:

    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.

    • Tom Urtis says:

      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.

  83. Rosemarie Linfoot says:

    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.

  84. nadir says:

    thank you so much,
    > how we can make it in add-ins to work with all excel workbook

  85. Javier Ortiz says:

    Thank you

  86. zai says:

    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

    • Tom Urtis says:

      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.

  87. Aaron says:

    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

    • Tom Urtis says:

      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.

      • Aaron says:

        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.

        • Tom Urtis says:

          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.

  88. Angela Cremeens says:

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

    • Tom Urtis says:

      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.

  89. Shanna says:

    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.

    • Tom Urtis says:

      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.

      • Shanna says:

        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.

        • Tom Urtis says:

          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

  90. Duraivel says:

    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.

    • Tom Urtis says:

      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.

  91. Luke Friesen says:

    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?

    • Luke Friesen says:

      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?

      • Tom Urtis says:

        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.

        • Luke Friesen says:

          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 🙂

  92. Maria says:

    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!

  93. Milad Mehrbod says:

    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.

    • Milad Mehrbod says:

      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.

      • Tom Urtis says:

        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.

        • Milad Mehrbod says:

          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.

          • Tom Urtis says:

            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

            • Milad Mehrbod says:

              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?

              • Tom Urtis says:

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

                • Milad Mehrbod says:

                  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.

                  • Tom Urtis says:

                    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.

                    • Milad Mehrbod says:

                      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.

                    • Tom Urtis says:

                      No worries. Thanks for the follow-up.

  94. Milad Mehrbod says:

    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?

  95. Milad Mehrbod says:

    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

  96. Sarah says:

    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

    • Tom Urtis says:

      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.

  97. Lucas says:

    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

    • Tom Urtis says:

      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.

Leave a Reply

Your email address will not be published. Required fields are marked *

*