I only want to change column 'K'.

Scenario: I filter the names in column K with a yellow highlight (so I know which people I need to work with), i.e. k10, k11, k12 are highlighted yellow. As I complete my work I change the cell (k10 to no fill). I would like my excel sheet to automatically update and remove the k10/no fill cell from the list.

Basically, when I change the cell to a 'no fill' I would like it to automatically disappear and only show the remaining highlighted cell (i.e k11 & k12).

Is this possible?

  • No, you'll need to reapply the sort or put in some VBA on the sheet to detect changes – Raystafarian Dec 11 '15 at 18:05

Unfortunately, excel won't "react" automatically to the change of a color (it simply doesn't trigger any event)...

On the other site: if you filter them out, you can simply right-click the row-number and hide it. If you set the filter to "select all" all rows in range will be shown again.

This way you can hide all rows you don't want to show up (and you won't need to change any color)

If you still want to macro it, you can do it like that:
Hit Alt+F11 to open up the VBA-Console and double-click on ThisWorkbook.

enter image description here
Then paste in the code-window:

Public Sub hideRow()
  If Selection.Interior.Pattern <> xlNone And Selection.Count = 1 Then
    Selection.Interior.Pattern = xlNone
    Selection.EntireRow.Hidden = True
  End If
End Sub

Now Save it as a .xlsm-file and afterwards click the quick access config button and then "More Commands..."
enter image description here

In the new window select the "Choose commands from" to "Macros" and change the "Customize Quick Access Toolbar" dropdown to "For [Workbookname].xlsm".
On the left site select "ThisWorkbook.hideRow" and then click "Add >>" at the center.
Optinal: Select the macro on the right side and hit "Modify...". With the new window, you can change the symbol and the name to be shown.
enter image description here
Click "OK" to accept all the changes.

Now, whenever this workbook is active, you will see your macro-symbol at the Quick Access Bar.
enter image description here

if you click it, while 1 cell is selected and has any fill, it will be set to "no fill" and the whole row will be hidden. If you change your filters, the hidden row will act like nothing ever happen to it.

If you need to change the way this macro works, just ask. Unfortunately i can't change the fact, that changing a cell-color can't trigger macros.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.