Formula for selecting highlighted cells only

Is there a formula I can use to capture only highlighted cells in a row?

Rick
Fri May 09 12:08:49 CDT 2008

What do you mean by "capture"? You can process each cell in a selection this
way...

Dim C As Range
For Each C In Selection
' Your code for each cell in selection would go here
Debug.Print C.Address, C.Value
Next

In the above example, the code will print out (in the Immediate window) the
address and value for each cell currently selected.

Rick


"Joey" <Joey@discussions.microsoft.com> wrote in message
news:A5DBC9E6-97AF-42DC-B5A2-FB5504973B89@microsoft.com...
> Is there a formula I can use to capture only highlighted cells in a row?

Joey
Fri May 09 12:16:04 CDT 2008

It's the results from a Conditional format. I now have a column of 5000
records with some highlighted and would like to move those highlighted cells
into another column.



"Rick Rothstein (MVP - VB)" wrote:

> What do you mean by "capture"? You can process each cell in a selection this
> way...
>
> Dim C As Range
> For Each C In Selection
> ' Your code for each cell in selection would go here
> Debug.Print C.Address, C.Value
> Next
>
> In the above example, the code will print out (in the Immediate window) the
> address and value for each cell currently selected.
>
> Rick
>
>
> "Joey" <Joey@discussions.microsoft.com> wrote in message
> news:A5DBC9E6-97AF-42DC-B5A2-FB5504973B89@microsoft.com...
> > Is there a formula I can use to capture only highlighted cells in a row?
>
>

Rick
Fri May 09 12:26:40 CDT 2008

Ah, I mistook what you meant by "highlighted". There are two approaches that
can be taken here... either examine the range, in code, for cells which meet
the criteria (formula) you used in your Conditional Format or examine the
cells for the specific format that was actually applied. Either way, you
need to tell us the formula/criteria you used in the Conditional Format and
also the specific formatting you applied.

Rick


"Joey" <Joey@discussions.microsoft.com> wrote in message
news:F47422FE-528B-4B35-83D8-0EBF57D8EC4E@microsoft.com...
> It's the results from a Conditional format. I now have a column of 5000
> records with some highlighted and would like to move those highlighted
> cells
> into another column.
>
>
>
> "Rick Rothstein (MVP - VB)" wrote:
>
>> What do you mean by "capture"? You can process each cell in a selection
>> this
>> way...
>>
>> Dim C As Range
>> For Each C In Selection
>> ' Your code for each cell in selection would go here
>> Debug.Print C.Address, C.Value
>> Next
>>
>> In the above example, the code will print out (in the Immediate window)
>> the
>> address and value for each cell currently selected.
>>
>> Rick
>>
>>
>> "Joey" <Joey@discussions.microsoft.com> wrote in message
>> news:A5DBC9E6-97AF-42DC-B5A2-FB5504973B89@microsoft.com...
>> > Is there a formula I can use to capture only highlighted cells in a
>> > row?
>>
>>

Joey
Fri May 09 12:32:01 CDT 2008

Formula is - =COUNTIF($A$1:$B$3395,B12)>1



"Rick Rothstein (MVP - VB)" wrote:

> Ah, I mistook what you meant by "highlighted". There are two approaches that
> can be taken here... either examine the range, in code, for cells which meet
> the criteria (formula) you used in your Conditional Format or examine the
> cells for the specific format that was actually applied. Either way, you
> need to tell us the formula/criteria you used in the Conditional Format and
> also the specific formatting you applied.
>
> Rick
>
>
> "Joey" <Joey@discussions.microsoft.com> wrote in message
> news:F47422FE-528B-4B35-83D8-0EBF57D8EC4E@microsoft.com...
> > It's the results from a Conditional format. I now have a column of 5000
> > records with some highlighted and would like to move those highlighted
> > cells
> > into another column.
> >
> >
> >
> > "Rick Rothstein (MVP - VB)" wrote:
> >
> >> What do you mean by "capture"? You can process each cell in a selection
> >> this
> >> way...
> >>
> >> Dim C As Range
> >> For Each C In Selection
> >> ' Your code for each cell in selection would go here
> >> Debug.Print C.Address, C.Value
> >> Next
> >>
> >> In the above example, the code will print out (in the Immediate window)
> >> the
> >> address and value for each cell currently selected.
> >>
> >> Rick
> >>
> >>
> >> "Joey" <Joey@discussions.microsoft.com> wrote in message
> >> news:A5DBC9E6-97AF-42DC-B5A2-FB5504973B89@microsoft.com...
> >> > Is there a formula I can use to capture only highlighted cells in a
> >> > row?
> >>
> >>
>
>

Joey
Fri May 09 12:40:34 CDT 2008

I also selected a lavendor color to highlight the cells with

"Joey" wrote:

> Formula is - =COUNTIF($A$1:$B$3395,B12)>1
>
>
>
> "Rick Rothstein (MVP - VB)" wrote:
>
> > Ah, I mistook what you meant by "highlighted". There are two approaches that
> > can be taken here... either examine the range, in code, for cells which meet
> > the criteria (formula) you used in your Conditional Format or examine the
> > cells for the specific format that was actually applied. Either way, you
> > need to tell us the formula/criteria you used in the Conditional Format and
> > also the specific formatting you applied.
> >
> > Rick
> >
> >
> > "Joey" <Joey@discussions.microsoft.com> wrote in message
> > news:F47422FE-528B-4B35-83D8-0EBF57D8EC4E@microsoft.com...
> > > It's the results from a Conditional format. I now have a column of 5000
> > > records with some highlighted and would like to move those highlighted
> > > cells
> > > into another column.
> > >
> > >
> > >
> > > "Rick Rothstein (MVP - VB)" wrote:
> > >
> > >> What do you mean by "capture"? You can process each cell in a selection
> > >> this
> > >> way...
> > >>
> > >> Dim C As Range
> > >> For Each C In Selection
> > >> ' Your code for each cell in selection would go here
> > >> Debug.Print C.Address, C.Value
> > >> Next
> > >>
> > >> In the above example, the code will print out (in the Immediate window)
> > >> the
> > >> address and value for each cell currently selected.
> > >>
> > >> Rick
> > >>
> > >>
> > >> "Joey" <Joey@discussions.microsoft.com> wrote in message
> > >> news:A5DBC9E6-97AF-42DC-B5A2-FB5504973B89@microsoft.com...
> > >> > Is there a formula I can use to capture only highlighted cells in a
> > >> > row?
> > >>
> > >>
> >
> >

Rick
Fri May 09 14:46:55 CDT 2008

That is probably the easier one to work with.

Dim C As Range
For Each C In Range("A1:B3395")
If .Cells.Interior.ColorIndex = ## Then
' Your code for each cell in selection would go here
Debug.Print C.Address, C.Value
End If
Next

Replace the ## above with the actual ColorIndex number used for you lavender
color. If you are not sure of the ColorIndex that is being used for it, get
VB to tell you. Make a mental note of the address for one of your cells that
is colored with this lavender color, let's say for this example, that A1 is
lavender colored. Go to the VB editor and execute this in the Immediate
window...

? Range("A1").Cells.Interior.ColorIndex

VB will print out the ColorIndex number it is using for that lavender
color... replace the ## in my code with that number.

Rick


"Joey" <Joey@discussions.microsoft.com> wrote in message
news:9B68F8D5-39DD-4F98-9F4D-0C9E6509FA23@microsoft.com...
>I also selected a lavendor color to highlight the cells with
>
> "Joey" wrote:
>
>> Formula is - =COUNTIF($A$1:$B$3395,B12)>1
>>
>>
>>
>> "Rick Rothstein (MVP - VB)" wrote:
>>
>> > Ah, I mistook what you meant by "highlighted". There are two approaches
>> > that
>> > can be taken here... either examine the range, in code, for cells which
>> > meet
>> > the criteria (formula) you used in your Conditional Format or examine
>> > the
>> > cells for the specific format that was actually applied. Either way,
>> > you
>> > need to tell us the formula/criteria you used in the Conditional Format
>> > and
>> > also the specific formatting you applied.
>> >
>> > Rick
>> >
>> >
>> > "Joey" <Joey@discussions.microsoft.com> wrote in message
>> > news:F47422FE-528B-4B35-83D8-0EBF57D8EC4E@microsoft.com...
>> > > It's the results from a Conditional format. I now have a column of
>> > > 5000
>> > > records with some highlighted and would like to move those
>> > > highlighted
>> > > cells
>> > > into another column.
>> > >
>> > >
>> > >
>> > > "Rick Rothstein (MVP - VB)" wrote:
>> > >
>> > >> What do you mean by "capture"? You can process each cell in a
>> > >> selection
>> > >> this
>> > >> way...
>> > >>
>> > >> Dim C As Range
>> > >> For Each C In Selection
>> > >> ' Your code for each cell in selection would go here
>> > >> Debug.Print C.Address, C.Value
>> > >> Next
>> > >>
>> > >> In the above example, the code will print out (in the Immediate
>> > >> window)
>> > >> the
>> > >> address and value for each cell currently selected.
>> > >>
>> > >> Rick
>> > >>
>> > >>
>> > >> "Joey" <Joey@discussions.microsoft.com> wrote in message
>> > >> news:A5DBC9E6-97AF-42DC-B5A2-FB5504973B89@microsoft.com...
>> > >> > Is there a formula I can use to capture only highlighted cells in
>> > >> > a
>> > >> > row?
>> > >>
>> > >>
>> >
>> >

Joey
Fri May 09 14:53:13 CDT 2008

Thank you very much!

"Rick Rothstein (MVP - VB)" wrote:

> That is probably the easier one to work with.
>
> Dim C As Range
> For Each C In Range("A1:B3395")
> If .Cells.Interior.ColorIndex = ## Then
> ' Your code for each cell in selection would go here
> Debug.Print C.Address, C.Value
> End If
> Next
>
> Replace the ## above with the actual ColorIndex number used for you lavender
> color. If you are not sure of the ColorIndex that is being used for it, get
> VB to tell you. Make a mental note of the address for one of your cells that
> is colored with this lavender color, let's say for this example, that A1 is
> lavender colored. Go to the VB editor and execute this in the Immediate
> window...
>
> ? Range("A1").Cells.Interior.ColorIndex
>
> VB will print out the ColorIndex number it is using for that lavender
> color... replace the ## in my code with that number.
>
> Rick
>
>
> "Joey" <Joey@discussions.microsoft.com> wrote in message
> news:9B68F8D5-39DD-4F98-9F4D-0C9E6509FA23@microsoft.com...
> >I also selected a lavendor color to highlight the cells with
> >
> > "Joey" wrote:
> >
> >> Formula is - =COUNTIF($A$1:$B$3395,B12)>1
> >>
> >>
> >>
> >> "Rick Rothstein (MVP - VB)" wrote:
> >>
> >> > Ah, I mistook what you meant by "highlighted". There are two approaches
> >> > that
> >> > can be taken here... either examine the range, in code, for cells which
> >> > meet
> >> > the criteria (formula) you used in your Conditional Format or examine
> >> > the
> >> > cells for the specific format that was actually applied. Either way,
> >> > you
> >> > need to tell us the formula/criteria you used in the Conditional Format
> >> > and
> >> > also the specific formatting you applied.
> >> >
> >> > Rick
> >> >
> >> >
> >> > "Joey" <Joey@discussions.microsoft.com> wrote in message
> >> > news:F47422FE-528B-4B35-83D8-0EBF57D8EC4E@microsoft.com...
> >> > > It's the results from a Conditional format. I now have a column of
> >> > > 5000
> >> > > records with some highlighted and would like to move those
> >> > > highlighted
> >> > > cells
> >> > > into another column.
> >> > >
> >> > >
> >> > >
> >> > > "Rick Rothstein (MVP - VB)" wrote:
> >> > >
> >> > >> What do you mean by "capture"? You can process each cell in a
> >> > >> selection
> >> > >> this
> >> > >> way...
> >> > >>
> >> > >> Dim C As Range
> >> > >> For Each C In Selection
> >> > >> ' Your code for each cell in selection would go here
> >> > >> Debug.Print C.Address, C.Value
> >> > >> Next
> >> > >>
> >> > >> In the above example, the code will print out (in the Immediate
> >> > >> window)
> >> > >> the
> >> > >> address and value for each cell currently selected.
> >> > >>
> >> > >> Rick
> >> > >>
> >> > >>
> >> > >> "Joey" <Joey@discussions.microsoft.com> wrote in message
> >> > >> news:A5DBC9E6-97AF-42DC-B5A2-FB5504973B89@microsoft.com...
> >> > >> > Is there a formula I can use to capture only highlighted cells in
> >> > >> > a
> >> > >> > row?
> >> > >>
> >> > >>
> >> >
> >> >
>
>