Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating macro code in Excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating macro code in Excel
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 "Paul" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating macro code in Excel
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 "Paul" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can anyone help with the code for creating a aut print macro... | Excel Discussion (Misc queries) | |||
Creating a code for a macro | Excel Discussion (Misc queries) | |||
Creating a macro in excel | Excel Discussion (Misc queries) | |||
Creating Macro or Code Help? | Excel Worksheet Functions | |||
Creating a macro in excel | Excel Worksheet Functions |