Return cell value based on colour

Excelguru87

New Member
Joined
Aug 17, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
On sheet 1 there are multiple columns and rows which have been colour coded. Is there a way on sheet 2 to return a cell value based on a certain colour? For example if cell a1 on sheet 1 is green I want cell a1 on sheet 2 to return that data, but be blank if it was any other colour?

I cant think of a way to filter multiple rows and columns by colour so wondered if the above was possible?
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Insert in an Excel workbook a module with next VBA code. Then, apply the given formula in the cells from the second column. It filters only the red font values, and can be inserted in another file, too. If you want, use a conditional format for the filtered cells, for them to be colored as the original ones.

VBA Code:
Function GetColorText(pRange As Range) As String
Dim xOut As String
Dim xValue As String
Dim i As Long
Dim TextColor
TextColor = RGB(255, 0, 0) 'colorindex RGB
xValue = pRange.Text
For i = 1 To VBA.Len(xValue)
  If pRange.Characters(i, 1).Font.Color = TextColor Then
  xOut = xOut & VBA.Mid(xValue, i, 1)
  End If
Next
GetColorText = xOut
End Function

Book1.xlsm
AB
11 
22 
33 
44 
55 
666
777
888
999
101010
Sheet1
Cell Formulas
RangeFormula
B1:B10B1=GetColorText(A1)
 
Last edited:
Upvote 0
Insert in an Excel workbook a module with next VBA code. Then, apply the given formula in the cells from the second column. It filters only the red font values, and can be inserted in another file, too. If you want, use a conditional format for the filtered cells, for them to be colored as the original ones.

VBA Code:
Function GetColorText(pRange As Range) As String
Dim xOut As String
Dim xValue As String
Dim i As Long
Dim TextColor
TextColor = RGB(255, 0, 0) 'colorindex RGB
xValue = pRange.Text
For i = 1 To VBA.Len(xValue)
  If pRange.Characters(i, 1).Font.Color = TextColor Then
  xOut = xOut & VBA.Mid(xValue, i, 1)
  End If
Next
GetColorText = xOut
End Function

Book1.xlsm
AB
11 
22 
33 
44 
55 
666
777
888
999
101010
Sheet1
Cell Formulas
RangeFormula
B1:B10B1=GetColorText(A1)
Thank you for the help.

Is there a way to return data based on cell fill colour not font colour?
 
Upvote 0
Thank you for the help.

Is there a way to return data based on cell fill colour not font colour?
It's too complicated and only by a macro. There is not a special formula, focused on this task. But it's easier if you apply a conditional format based on the font color, in order to get exactly the desired result.

If that resolve the problem then it'd be better to mark the discussion as Resoved; see the procedure: Mark as Solution
 
Upvote 0
Thank you for the help.

Is there a way to return data based on cell fill colour not font colour?
See, based on conditional formatting.
Book1.xlsm
A
1 
2 
3 
4 
5 
66
77
88
99
1010
Sheet2
Cell Formulas
RangeFormula
A1:A10A1=Sheet1!B1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A:ACelldoes not contain a blank value textNO
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top