Creating macro code in Excel

[Paul]

Hi There,

Can anyone give me some tips or idea on how to create a
macro that would change the color of my data in a table.
Please see my excel sample table below:

look up table: 10 20 30 40 50 60

Col1 Col2 Col3 Col4 Col5 Col6
Row1 10 11 12 13 14 15
Row2 16 17 18 19 20 21
..
..
..
Row10 35 36 37 38 39 40

Here how it goes, for example in the look up table I have
10 and when I find 10 in Row1/Col1 I would like to change
it to color RED, Then, If I have 20 in lookup table and I
find it from Row2/Col5 I would change it to color BLUE,
and so on.

Can anybody have an idea on this?

Appreciate your help. thanks
Paul

 

[Cecilkumara Fernando]

Paul,
This will give you a start,
Cecil

Sub Macro1()
Range("A1").Select
For i = 1 To 5
Cells.Find(What:=i * 10, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Interior.ColorIndex = i + 10
Next i
End Sub

 

[Dan E]

Paul,

Give this a try

Sub CheckMatch()
Dim R1 As Range, R2 As Range
Dim i As Integer, j As Integer
Dim color() As Integer
Set R1 = Application.InputBox("Select Lookup Table", Type:=8)
Set R2 = Application.InputBox("Select Data Table", Type:=8)
ReDim color(R1.Count) As Integer
For i = 1 To R1.Count
color(i) = R1(i).Font.ColorIndex
Next
For j = 1 To R2.Count
For i = 1 To R1.Count
If R1(i) = R2(j) Then
R2(j).Font.ColorIndex = color(i)
End If
Next
Next
End Sub

It takes the colors from the lookup table so color your
lookup table before you run it