Copy color of cell by value

[arthur_w]

I have a collumn of values 100, 101, 102 etc, and i want their color to automatically match to the values in another worksheet (but this values are in a matrix...)
I had:
"Me.Range("A3").Interior.Color = Worksheets("WS1").Range("B6").Interior.Color" for each one, but thats an awfull lot of repetition

Any ideia on how to just 'search all column values in matrix and match colors'?

Thanks!

 

[arthur_w]

Me.Range("A3").Interior.Color = Worksheets("LeChaim").Range("B6:E14").Find(Me.Range("A4")).Interior.Color

Slightly updated for it to search for it self.. but miss how to do the loop

 

[arthur_w]

Sub Worksheet_SelectionChange(ByVal Target As Range)
For c = Range("A3:A38").Row To (Range("A3:A38").Row + Range("A3:A38").Rows.Count - 1)
Me.Range("A" & c).Interior.Color = Worksheets("WORKSHEET").Range("B6:E14").Find(Me.Range("A" & c)).Interior.Color
Next c
End Sub



Who would have thought it... never had written before, probably dirty because of taking out the row and then adding A after, but well, it works

 

[Eric W]

Getting it to work at all is always the first step! Well done! However, it can be improved a bit:

For Each c in Range("A3:A38")
    c.Interior.Color = Worksheets("WORKSHEET").Range("B6:E14").Find(c.Value).Interior.Color
Next c

 

[arthur_w]

Getting it to work at all is always the first step! Well done! However, it can be improved a bit:

For Each c in Range("A3:A38")
    c.Interior.Color = Worksheets("WORKSHEET").Range("B6:E14").Find(c.Value).Interior.Color
Next c

Thanks for cleaning it up, i have finalizedit to the full purpose of the initial question and already added it to it:

Sub Worksheet_SelectionChange(ByVal Target As Range)

'Ranges so i can apply it to different worksheets:

Dim Rows As Range
Set Rows = Range("A2:A37")
Dim Columns As Range
Set Columns = Range("B1:G1")
Dim matrix As Range
Set matrix = Sheets("WorkSheet").Range("B6:E14")

'turns colors into the same

For Each c In Rows
c.Interior.Color = matrix.Find(c.Value).Interior.Color
Next c

'Stores date in the correct cell based on color change and if it hasnt any data on (so that it doesnt keep rewritting everytime it runs, and doesnt errase manual inputs)

For Each a In Columns
For Each b In Rows
If a.Interior.Color = b.Interior.Color And IsEmpty(Cells(b.Row, a.Column)) Then
Cells(b.Row, a.Column).Value = Date
End If
Next b
Next a

End Sub

 

[syed2003]

I have a collumn of values 100, 101, 102 etc, and i want their color to automatically match to the values in another worksheet (but this values are in a matrix...)
I had:
"Me.Range("A3").Interior.Color = Worksheets("WS1").Range("B6").Interior.Color" for each one, but thats an awfull lot of repetition

Any ideia on how to just 'search all column values in matrix and match colors'?

Thanks!

Sub RectangleRoundedCorners1_Click()
Dim FirstMatch As Long
Dim SecondMatch As Long
Dim RowCt As Long
Dim CIndex
RowCt = Sheets("Sheet1").Range("D2").End(xlDown).Row
For FirstMatch = 2 To RowCt
For SecondMatch = FirstMatch + 1 To RowCt
If Sheets("Sheet1").Range("D" & FirstMatch).Value = Sheets("Sheet1").Range("D" & SecondMatch).Value Then
Sheets("Sheet1").Range("D" & FirstMatch).Interior.ColorIndex = SecondMatch
Sheets("Sheet1").Range("D" & SecondMatch).Interior.ColorIndex = SecondMatch
End If
Next SecondMatch
Next FirstMatch
End Sub