macro change formatting
 

Help Please€¦ I am new to righting macros in visual basics. I have an excel
sheet I distribute to others. What I need is a macro that will change the
color of a cell (green) and the color of the text (red) when the information
in the cell has been changed.

macro change formatting
 

How would the change take place?

Manually or by formula?

One-time change like from 1234 to 5678?

You could probably use Conditional Formatting for a one-timer for either.

=$D2<1234 and format to green colour and red font.

Or worksheet_change event code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value < "" Then
.Font.ColorIndex = 3
.Interior.ColorIndex = 10
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that module.


Gord Dibben MS Excel MVP

On Mon, 30 Jul 2007 11:08:03 -0700, Lisa wrote:

Help Please… I am new to righting macros in visual basics. I have an excel
sheet I distribute to others. What I need is a macro that will change the
color of a cell (green) and the color of the text (red) when the information
in the cell has been changed.

macro change formatting
 

Thank you so much for the help. It works like a charm.


"Gord Dibben" wrote:

How would the change take place?

Manually or by formula?

One-time change like from 1234 to 5678?

You could probably use Conditional Formatting for a one-timer for either.

=$D2<1234 and format to green colour and red font.

Or worksheet_change event code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value < "" Then
.Font.ColorIndex = 3
.Interior.ColorIndex = 10
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that module.


Gord Dibben MS Excel MVP

On Mon, 30 Jul 2007 11:08:03 -0700, Lisa wrote:

Help Please€¦ I am new to righting macros in visual basics. I have an excel
sheet I distribute to others. What I need is a macro that will change the
color of a cell (green) and the color of the text (red) when the information
in the cell has been changed.