Extract Cell Value Based on Cell Color Within a Row Range

ExcelSammie

New Member
Joined
Jul 3, 2018
Messages
3
Hello, I am new here. I am trying to extract the cell value based on the cell color within a row range. I have looked around and haven't found anything that has worked for me.

So I am trying to lookup between B2:Q2 the cells that are colored magenta and then return the cell value of the magenta cell into S2. I need to do this for thousands of rows. :confused:

 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
Re: Help: Extract Cell Value Based on Cell Color Within a Row Range

How are those cells being coloured?
 

ExcelSammie

New Member
Joined
Jul 3, 2018
Messages
3
Re: Help: Extract Cell Value Based on Cell Color Within a Row Range

I am manually coloring the cells
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
Re: Help: Extract Cell Value Based on Cell Color Within a Row Range

In that case how about this function
Code:
Function GetColorValue(Rng As Range)
   Dim cl As Range
   For Each cl In Rng
      If cl.Interior.Color = vbMagenta Then GetColorValue = cl.Value: Exit For
   Next cl
End Function
and enter this in S2 =GetColorValue(B2:Q2)
 

ExcelSammie

New Member
Joined
Jul 3, 2018
Messages
3
Re: Help: Extract Cell Value Based on Cell Color Within a Row Range

In that case how about this function
Code:
Function GetColorValue(Rng As Range)
   Dim cl As Range
   For Each cl In Rng
      If cl.Interior.Color = vbMagenta Then GetColorValue = cl.Value: Exit For
   Next cl
End Function
and enter this in S2 =GetColorValue(B2:Q2)


Wonderful! This works perfect. Thank you so much!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
Re: Help: Extract Cell Value Based on Cell Color Within a Row Range

Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,108,973
Messages
5,525,988
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top