Select highlighted cells

February 22, 2011 at 11:49:09
Specs: Windows 7
Is there a way to create a macro that selects all cells by color? Lets say I were to have a random set of 15 cells in a set of 40-cell data that were highlighted in yellow, is there a way to automatically select them, like via button? Slightly related to a similar post I made two days ago.
Thank you in advance

See More: Select highlighted cells

Report •

#1
February 22, 2011 at 12:00:24
Let's say the range in question is A1:B20.

Let's say the highlighted cells are Yellow.

Let's say this code should do it:

Sub SelectColoredCells()
'Loop through range
 For Each c_cell In Range("A1:B20")
'If cell is yellow, add it's address to the string
    If c_cell.Interior.ColorIndex = 6 Then
      tmpRng = tmpRng & c_cell.Address & ","
     End If
  Next
'Strip off extra comma from string
      myRng = Left(tmpRng, Len(tmpRng) - 1)
'Select the range
      Range(myRng).Select
End Sub

Note: this will not work for cells that are colored yellow via Conditional Formatting.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 22, 2011 at 12:05:36
I don't have a fixed range to work with. My graph is divided and sectioned according to different variables. One night I may want to select: C3, C8, C19, C7, C15.
It will be consistently random, and I will be dealing with those specified numbers for hours at a time, and the next day it will be changed. I need a way to start off the night selecting the specific cells I will be working with, then being able to select them all later when I need them to be selected. Unless that is not possible.

Report •

#3
February 22, 2011 at 15:13:08
So basically, I need a code just like the one you posted, but instead of being for a set series of cells, it would only be the highlighted ones, seeing as how it will be changing often.

Report •

Related Solutions

#4
February 22, 2011 at 19:48:22
You have totally confused me. It was understandable when you said you wanted to select cells that were highlighted in yellow.

But this makes no sense:

I need a way to start off the night selecting the specific cells I will be working with, then being able to select them all later when I need them to be selected.

The code I posted will select all of the Yellow cells within the range specified.

If you want to select all of the Yellow cells within a range that you have already selected in the worksheet, try this:

Sub SelectColoredCells_v1()
'Loop through selected range
 For Each c_cell In Selection
'If cell is yellow, add it's address to the string
    If c_cell.Interior.ColorIndex = 6 Then
      tmpRng = tmpRng & c_cell.Address & ","
     End If
  Next
'Strip off extra comma from string
      myRng = Left(tmpRng, Len(tmpRng) - 1)
'Select the range
      Range(myRng).Select
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question