Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros to color cells
Hello all, I was wondering if there was a macro or some other way to
highlight cells when there is a change. For example I have two tabs, one original and one revised, for all of the numbers that change in the revised tab I would like to automatically highlight so we can easily track them. Is there a way to do this? Thanks...Peter |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros to color cells
Sub auditt()
Dim sh1 As Worksheet, sh2 As Worksheet Set sh1 = Sheets("original") Set sh2 = Sheets("revised") For Each r In sh1.UsedRange v1 = r.Value rr = r.Row cc = r.Column v2 = sh2.Cells(rr, cc).Value If v1 < v2 Then sh2.Cells(rr, cc).Interior.ColorIndex = 3 End If Next End Sub -- Gary''s Student - gsnu200748 "Peter" wrote: Hello all, I was wondering if there was a macro or some other way to highlight cells when there is a change. For example I have two tabs, one original and one revised, for all of the numbers that change in the revised tab I would like to automatically highlight so we can easily track them. Is there a way to do this? Thanks...Peter |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros to color cells
Thanks, that worked great. Two more things.
1. What number do I use for the color of light yellow? 2. How do I create a box or button to put the macro into so I can toggle it on or off? "Gary''s Student" wrote: Sub auditt() Dim sh1 As Worksheet, sh2 As Worksheet Set sh1 = Sheets("original") Set sh2 = Sheets("revised") For Each r In sh1.UsedRange v1 = r.Value rr = r.Row cc = r.Column v2 = sh2.Cells(rr, cc).Value If v1 < v2 Then sh2.Cells(rr, cc).Interior.ColorIndex = 3 End If Next End Sub -- Gary''s Student - gsnu200748 "Peter" wrote: Hello all, I was wondering if there was a macro or some other way to highlight cells when there is a change. For example I have two tabs, one original and one revised, for all of the numbers that change in the revised tab I would like to automatically highlight so we can easily track them. Is there a way to do this? Thanks...Peter |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros to color cells
Peter
Answer to 1. See David McRitchie's site for the color palette. http://www.mvps.org/dmcritchie/excel/colors.htm Or just run this macro from John Walkenbach Sub ListColorIndexes() Dim Ndx As Long Sheets.Add For Ndx = 1 To 56 Cells(Ndx, 1).Interior.ColorIndex = Ndx Cells(Ndx, 2).Value = Hex(ThisWorkbook.Colors(Ndx)) Cells(Ndx, 3).Value = Ndx Next Ndx End Sub Gord Dibben MS Excel MVP On Wed, 3 Oct 2007 14:36:01 -0700, Peter wrote: Thanks, that worked great. Two more things. 1. What number do I use for the color of light yellow? 2. How do I create a box or button to put the macro into so I can toggle it on or off? "Gary''s Student" wrote: Sub auditt() Dim sh1 As Worksheet, sh2 As Worksheet Set sh1 = Sheets("original") Set sh2 = Sheets("revised") For Each r In sh1.UsedRange v1 = r.Value rr = r.Row cc = r.Column v2 = sh2.Cells(rr, cc).Value If v1 < v2 Then sh2.Cells(rr, cc).Interior.ColorIndex = 3 End If Next End Sub -- Gary''s Student - gsnu200748 "Peter" wrote: Hello all, I was wondering if there was a macro or some other way to highlight cells when there is a change. For example I have two tabs, one original and one revised, for all of the numbers that change in the revised tab I would like to automatically highlight so we can easily track them. Is there a way to do this? Thanks...Peter |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros to color cells
One more way to answer #1.
Record a macro when you change the color to what you like. Stop recording and look at that code. Then delete that macro. I'll often start a new workbook, do that recording and close without saving. For #2. View|toolbars|show the Forms toolbar click on the button icon and add it to your sheet. You'll be prompted to assign a macro. If you don't do that assignment right away, you can rightclick on that button and choose Assign Macro. Remember, this is a button from the Forms toolbar--not the commandbutton from the Control toolbox toolbar. Peter wrote: Thanks, that worked great. Two more things. 1. What number do I use for the color of light yellow? 2. How do I create a box or button to put the macro into so I can toggle it on or off? "Gary''s Student" wrote: Sub auditt() Dim sh1 As Worksheet, sh2 As Worksheet Set sh1 = Sheets("original") Set sh2 = Sheets("revised") For Each r In sh1.UsedRange v1 = r.Value rr = r.Row cc = r.Column v2 = sh2.Cells(rr, cc).Value If v1 < v2 Then sh2.Cells(rr, cc).Interior.ColorIndex = 3 End If Next End Sub -- Gary''s Student - gsnu200748 "Peter" wrote: Hello all, I was wondering if there was a macro or some other way to highlight cells when there is a change. For example I have two tabs, one original and one revised, for all of the numbers that change in the revised tab I would like to automatically highlight so we can easily track them. Is there a way to do this? Thanks...Peter -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I apply color in cells ..color now shown only in print prev | Excel Worksheet Functions | |||
can macros change cell color daily, i.e. green to red everyday? | Excel Worksheet Functions | |||
Excel 2003 will not display color fonts or color fill cells | Excel Worksheet Functions | |||
My excel 2003 wont let me fill cells with color or color the tabs. | New Users to Excel | |||
Is there a way to sort by color in excel? using macros? | Excel Worksheet Functions |