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

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-04-2017, 06:32 PM
FUGMAN FUGMAN is offline Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow Windows 10 Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow Office 2016
Banned
Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow
 
Join Date: Feb 2017
Posts: 55
FUGMAN is on a distinguished road
Default Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow

I am looking for an Excel Macro that will do the following two tasks.



1. Clear all row colors within a range starting at cell A8 and ending at the last record in column A. The row of the last record in column A can be any row above row 8.

2. Then make the row of the active cell to be colored yellow.

Thanking you in advance for any help regarding this issue.
Reply With Quote
  #2  
Old 02-04-2017, 07:26 PM
jeffreybrown jeffreybrown is offline Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow Windows Vista Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow Office 2007
Expert
 
Join Date: Apr 2016
Posts: 549
jeffreybrown will become famous soon enoughjeffreybrown will become famous soon enough
Default

Not entirely clear...

Quote:
starting at cell A8 and ending at the last record in column A
I would think ending means, start at A8 and the last row could be A255!

Quote:
the last record in column A can be any row above row 8
Above row 8, then wouldn't this just be row 1 thru row 8?

Quote:
Then make the row of the active cell to be colored yellow
What establishes the active row?

Can you give a little more description please or even a sample workbook with before and after?
Reply With Quote
  #3  
Old 02-04-2017, 09:22 PM
FUGMAN FUGMAN is offline Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow Windows 10 Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow Office 2016
Banned
Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow
 
Join Date: Feb 2017
Posts: 55
FUGMAN is on a distinguished road
Default

Thank you for your reply to my thread. Sorry for the confusion. Hopefully this additional input from me will make things more clear.

First step is to clear all colors from rows within a range starting at cell A8 and ending at the last row which contains data in column A

To address your questions...
The last row could be A9 or A255, A355 or beyond...depending on how many total rows with data are in column A of the spreadsheet.

My statement "above row 8" is in reference to any row number above 8. My intention is to have the macro function for all rows except rows 1 through 7.

The row to be colored is to be determined by selecting a cell (Active Cell) in the row (any row except for rows 1 through 7) that I wish to be colored yellow.
Reply With Quote
  #4  
Old 02-04-2017, 11:11 PM
FUGMAN FUGMAN is offline Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow Windows 10 Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow Office 2016
Banned
Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow
 
Join Date: Feb 2017
Posts: 55
FUGMAN is on a distinguished road
Default

Additional attempt for clarification of what I am hoping to accomplish.
I want to be able to change the color of a row in my spreadsheet to yellow. I want to select that row by selecting a cell in the row that I want to be colored yellow. This is to include all rows that have contiguous data in column A, excluding rows at 1 through 7. (Starting with row at A8 plus all additional rows numbered higher than row A8 that contain data...till a blank is encountered in column A).
In addition to my original inquiry, I would also like to limit this activity to columns A thru BZ.
Before I have the color of the desired row changed to yellow, I want to have any colors removed from all rows , except for rows 1 through 7.
Reply With Quote
  #5  
Old 02-04-2017, 11:58 PM
NoSparks NoSparks is offline Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow Windows 7 64bit Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 735
NoSparks will become famous soon enoughNoSparks will become famous soon enough
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
  #6  
Old 02-05-2017, 12:17 AM
FUGMAN FUGMAN is offline Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow Windows 10 Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow Office 2016
Banned
Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow
 
Join Date: Feb 2017
Posts: 55
FUGMAN is on a distinguished road
Default

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)?
Reply With Quote
  #7  
Old 02-05-2017, 08:03 AM
NoSparks NoSparks is offline Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow Windows 7 64bit Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 735
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

Sorry for the delay in getting back (went to bed for the night).

Credit for this solution goes to whoever won the "I was first" debate at the link I provided, not Me.
I thought this was quite nifty when I saw it so tucked it away for future reference.

Hopefully you've experimented with the macro and by now satisfied the additional requirements.
If not, change the name of the sub to something like Public Sub Highlight_Row()
and in the Intersect line change Target to ActiveCell
Reply With Quote
  #8  
Old 02-05-2017, 08:37 AM
FUGMAN FUGMAN is offline Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow Windows 10 Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow Office 2016
Banned
Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow
 
Join Date: Feb 2017
Posts: 55
FUGMAN is on a distinguished road
Default

NoSparks.....I failed to recognize the change "Target to ActiveCell requirement.
After incorporating your solution my goal has been accomplished. You may not want credit for the solution, but as far as I am concerned, YOU resolved my issue.

Thank you for taking the time to assist me.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow 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
Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow 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


All times are GMT -7. The time now is 04:24 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft