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: , , , , , , , , , , , , ,
179 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.

      • 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

  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.

  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

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

  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.

Leave a Reply

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

*