Speed up selected cell fill colour macro

[Milos]

Hi all,

I am unsuccessfully trying to speed up my un-highlight selected cells macro. I have been toying with the code to speed it up as if I mistakenly select an entire row it takes a long time to complete. But I am too useless and have failed to get it working correctly. I have also been trying to insert the 'Do Until' code and putting in a loop.

What I am after is for the macro to search through all of the selected cells and if the cell has no fill colour applied then it will skip to the next selected cell with a fill colour. For all other cells that are filled with colour the macro will apply a colour index = 0.

Here is my faulty code:

Sub Un_Highlight_Selected_Cells()
   
   Application.ScreenUpdating = False
   Dim Rng As Range, cell As Range
   Set Rng = Selection
   For Each cell In Rng
   If cell.Interior.ColorIndex = xlColorIndexNone Then
   cell.Offset(0, 1).Select
   
Else: cell.Interior.ColorIndex = 0
   Next cell
   
    Application.ScreenUpdating = True
   
End Sub

Any help is appreciated,
Thanks

 

[James006]

Hello,

In order to make your life easier ...in your Sheet module ... you could have :

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
   Selection.Interior.ColorIndex = xlColorIndexNone
   Cancel = True
End Sub

Hope this will help

 

[Milos]

Thanks James, I was kind of hoping that there was a module based solution so I could store this in my personal modules as a lot of the spreadsheets that I use are password protected. But this is great for all the other spreadsheets!

 

[James006]

Hello,

[COLOR=#333333]Sub Un_Highlight_Selected_Cells()[/COLOR]
   [COLOR=#333333]Selection.Interior.ColorIndex = xlColorIndexNone[/COLOR]
End Sub

HTH

 

[Milos]

Wow that is so much simpler than my previous code! I do like to take the long route. Thanks heaps James

 

[James006]

You are welcome ...