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

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

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

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

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