Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow

NoSparks · #1 02-04-2017, 11:58 PM

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

FUGMAN · #2 02-05-2017, 12:17 AM

Thanks NoSparks.

Your proposed solution works as an event driven procedure, meeting the requirements as I specified.

What I failed to indicate, in my inquiries and attempt s for clarification, is that I want to assign a button (form control) to a macro so that the macro will run only if I click on the button. Is it possible to use the code you have presented in a way that it can be in form of a regular macro..... which is run ONLY because of clicking the button (which is to be shown on the spreadsheet)?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
If value of cell A Matches a value in a Range of cells (column) then add value of cell A to cell C	rick10r	Excel	1	07-05-2016 12:07 PM
Dymanic Range Starting at a Cell Defined by a Count	jap7675	Excel Programming	7	12-02-2015 02:12 AM
If id cell range is empty then should not allow to fill any other cell	ubns	Excel Programming	2	04-12-2015 06:31 AM
Change formula cell range based on cell value	Scoth	Excel	4	10-25-2012 07:51 AM
How can I fill cell color starting from Cell D5 using Conditional formatting instead	Learner7	Excel	0	07-08-2010 05:50 AM