Change fill color based on the value on sheet2

[harky]

Sheet1 is data
Sheet2 is data with fill color

Abit hard to use conditional Formatting as i have at least 411 data with fill color


Is there anyway to do it?

 

[harky]

There are many data on sheet1 & 2 but i will show 5 here for example

Sheet1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Col A[/TD]
[/TR]
[TR]
[TD]0.1[/TD]
[/TR]
[TR]
[TD]0.2[/TD]
[/TR]
[TR]
[TD]0.3[/TD]
[/TR]
[TR]
[TD]0.4[/TD]
[/TR]
[TR]
[TD]0.5[/TD]
[/TR]
</tbody>[/TABLE]

I dunno how to put fill colour here.

Sheet2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Col A[/TD]
[/TR]
[TR]
[TD]0.1 [/TD]
[/TR]
[TR]
[TD]0.2[/TD]
[/TR]
[TR]
[TD]0.3[/TD]
[/TR]
[TR]
[TD]0.4[/TD]
[/TR]
[TR]
[TD]0.5[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Fnd As Range
   
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 1 Then
      Set Fnd = Sheets("Sheet2").Range("A:A").Find(Target.Value, , xlValues, xlWhole, , , False, , False)
      If Fnd Is Nothing Then
         Target.Interior.Color = xlNone
      Else
         Target.Interior.Color = Fnd.Interior.Color
      End If
   End If
End Sub

 

[harky]

thanks!
was thinking if possible, dont want to use macro.

Is conditional format the only way?

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Fnd As Range
   
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 1 Then
      Set Fnd = Sheets("Sheet2").Range("A:A").Find(Target.Value, , xlValues, xlWhole, , , False, , False)
      If Fnd Is Nothing Then
         Target.Interior.Color = xlNone
      Else
         Target.Interior.Color = Fnd.Interior.Color
      End If
   End If
End Sub

 

[harky]

Think i use this. Thanks bro!

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Fnd As Range
   
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 1 Then
      Set Fnd = Sheets("Sheet2").Range("A:A").Find(Target.Value, , xlValues, xlWhole, , , False, , False)
      If Fnd Is Nothing Then
         Target.Interior.Color = xlNone
      Else
         Target.Interior.Color = Fnd.Interior.Color
      End If
   End If
End Sub

 

[Fluff]

Without a macro, yes & you will need a rule for each individual colour.