Change fill color based on the value on sheet2

harky

Active Member
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

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

Sheet1
Col A
0.1
0.2
0.3
0.4
0.5

<tbody>
</tbody>

I dunno how to put fill colour here.

Sheet2
Col A
0.1
0.2
0.3
0.4
0.5

<tbody>
</tbody>
 

Fluff

MrExcel MVP, Moderator
How about
Code:
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

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

Is conditional format the only way?

How about
Code:
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

Active Member
Think i use this. Thanks bro!

How about
Code:
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
 

Some videos you may like

This Week's Hot Topics

Top