Fill values in another sheet based on the for the cells coloured in one color

[Tharabai]

Hi,Can someone help me on the below requirement.I have an sheet in which up from the column H the cell will be colored based on the cell value. For ex, if the cell value is 6 it will be colored in RED. Similarly GREEN, ORANGE, YELLOW and other colors.My Requirement is I will find the cells which are colored in RED from Column H and paste the respective values for the column in sheet 2.There will be specific columns in sheet 2 for which values from Sheet1 to be filled.Can this be done using macro.. if so, can someone shed some inputs on this pls...Sample sheet attached.Thanks,Tharabai

[Tharabai]

Urgent pls !!!

[dxider]

Ok.

Create this function as Public on a new Module:

Public Function SearchRed(valor As String, rango As Range) As String
Dim columna As String
columna = "N/A"
If valor <> "" Then

For i = 1 To rango.Columns.Count
If rango.Cells(1, i).Interior.Color = vbRed Then
columna = i
End If
Next
End If
SearchRed = columna
End Function


Then use this on the K cell in Sheet2:

=OFFSET(Sheet1!H1,0,SearchRed(Sheet1!A2,Sheet1!H2:O2))

When the Formula returns error, then there is no value on red, maybe some error handling will take care of this issue.

[dxider]

You can also add an "Exit For" when the If finds a match to speed things up a bit.

[Tharabai]

Hi, Thank you so much for the coding and the formula... its very new to me...Also, the value differs. The red color is in column I (c2) but the result shown as C3. And the other columns are not filled in the sheet2.Whether this coding will work if I have two reds in the same rows and if so, will they be displayed in separate rows in sheet 2.- Tharabai

[dxider]

Well, I think that I messed with the formula.

It has to be something like this:
=OFFSET(Sheet1!G1,0,SearchRed(Sheet1!A2,Sheet1!H2:O2))

If you wish to detect if there are 2 reds, this code will not work, since the function returns a the number of column that has the red.
Let me examine how to do it and I will reply later with a zipped file with the solution.

It's kind of trivial to fill the rest of the columns, the hard part is the detection of the reds.

[dxider]

There you go:


Public Function SearchRedV2(valor As String, rango As Range, rangoCs As Range) As String
Dim columna As String
columna = ""
If valor <> "" Then
For i = 1 To rango.Columns.Count
If rango.Cells(1, i).Interior.Color = vbRed Then
If columna = "" Then
columna = rangoCs.Cells(1, i).FormulaR1C1
Else
columna = columna & ", " & rangoCs.Cells(1, i).FormulaR1C1
End If
End If
Next
End If
SearchRedV2 = columna
End Function


And for the call, use this on the K column:


=SearchRedv2(Sheet1!A2,Sheet1!H2:O2,Sheet1!$H$1:$O$1)

The range with the C's is frozen to avoid it moving when you drag the formula down.

[Tharabai]

Sorry to bother you again on this and thank you so much for your quick response.I think I have not made my requirement clear. I want to check for the red fields in column H:O, if there are any reds, I want to fill all the values of in RED in sheet2 (each in single line) and fill the corresponding values. The coding provided above gives the cell names in which the RED are there...Can this also be done using macro... pls...- Tharabai

[Tharabai]

Any Clue ... :(

[Tharabai]

pls guide me.... very urgent !!!

[dxider]

Let me see what I can do.