View Single Post
 
Old 02-04-2017, 11:58 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Perhaps this will work... adapted from http://www.mrexcel.com/forum/excel-q...cted-cell.html

Assumes your colors are not conditional formatting.

Paste into the sheet module.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim lr As Long
  Dim rng As Range
  
  lr = Range("A8").End(xlDown).Row
  Set rng = Range("A8:BZ" & lr)
  
  rng.Interior.ColorIndex = 0
  
  On Error Resume Next
  With Application
    .FindFormat.Clear
    .ReplaceFormat.Clear
    .FindFormat.Interior.ColorIndex = 27
    .ReplaceFormat.Interior.ColorIndex = xlNone
    ActiveSheet.UsedRange.Replace What:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=True
    .FindFormat.Interior.ColorIndex = xlNone
    .ReplaceFormat.Interior.ColorIndex = 27
    Intersect(Target.EntireRow, rng).Replace What:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=True
  End With
End Sub
Reply With Quote