I have a worksheet named Data that I would like for a row to be
highlighted when someone enters Paid Off, Missing and Other . How do
I do a macro to perform this? I would like each type to be a
different color such as Paid off to be highlighted yellow, Missing
red,and Other pink.
Please help

Have you considered using a conditional format to do this? I also presume
that Missing/Paid Off/Other are all entered in the same column? If so, you
could set a condition for values on that column to change the entire row.

Come back if you need more help.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Maggie" wrote:

I have a worksheet named Data that I would like for a row to be
highlighted when someone enters Paid Off, Missing and Other . How do
I do a macro to perform this? I would like each type to be a
different color such as Paid off to be highlighted yellow, Missing
red,and Other pink.
Please help

On Jan 28, 2:43*pm, Barb Reinhardt
wrote:
Have you considered using a conditional format to do this? * *I also presume
that Missing/Paid Off/Other are all entered in the same column? *If so, you
could set a condition for values on that column to change the entire row. *

Come back if you need more help.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Maggie" wrote:
I have a worksheet named Data that I would like for a row to be
highlighted when someone enters Paid Off, Missing and Other . *How do
I do a macro to perform this? *I would like each type to be a
different color *such as Paid off to be highlighted yellow, Missing
red,and Other pink.
Please help- Hide quoted text -

- Show quoted text -

I have tried conditional formatting but it will only highlight the
cell. The data entered is in three different columns.

On Jan 28, 2:49*pm, Maggie wrote:
On Jan 28, 2:43*pm, Barb Reinhardt





wrote:
Have you considered using a conditional format to do this? * *I also presume
that Missing/Paid Off/Other are all entered in the same column? *If so, you
could set a condition for values on that column to change the entire row. *

Come back if you need more help.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.

"Maggie" wrote:
I have a worksheet named Data that I would like for a row to be
highlighted when someone enters Paid Off, Missing and Other . *How do
I do a macro to perform this? *I would like each type to be a
different color *such as Paid off to be highlighted yellow, Missing
red,and Other pink.
Please help- Hide quoted text -

- Show quoted text -

I have tried conditional formatting but it will only highlight the
cell. *The data entered is in three different columns.- Hide quoted text -

- Show quoted text -

And the data is coming from a drop down.

On 28 jan, 20:28, Maggie wrote:
I have a worksheet named Data that I would like for a row to be
highlighted when someone enters Paid Off, Missing and Other . *How do
I do a macro to perform this? *I would like each type to be a
different color *such as Paid off to be highlighted yellow, Missing
red,and Other pink.
Please help

Hai Maggie,

For starters VBA has 8 shorthand colors
vbBlack
vbRed
vbGreen
vbYellow
vbBlue
vbMagenta
vbCyan
vbWhite

Next to this you can use a colorindex.

To come back to your question:
Copy the macro below into the macropar of the sheet where you want the
colorchanging occur

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Value
Case "Paid off": Target.EntireRow.Interior.Color = vbYellow
Case "Missing": Target.EntireRow.Interior.Color = vbRed
Case "Other": Target.EntireRow.Interior.Color = vbMagenta
End Select
End Sub


HTH,
Wouter

On Jan 28, 2:56*pm, RadarEye wrote:
On 28 jan, 20:28, Maggie wrote:

I have a worksheet named Data that I would like for a row to be
highlighted when someone enters Paid Off, Missing and Other . *How do
I do a macro to perform this? *I would like each type to be a
different color *such as Paid off to be highlighted yellow, Missing
red,and Other pink.
Please help

Hai Maggie,

For starters VBA has 8 shorthand colors
vbBlack
vbRed
vbGreen
vbYellow
vbBlue
vbMagenta
vbCyan
vbWhite

Next to this you can use a colorindex.

To come back to your question:
Copy the macro below into the macropar of the sheet where you want the
colorchanging occur

Private Sub Worksheet_Change(ByVal Target As Range)
* * Select Case Target.Value
* * * * Case "Paid off": Target.EntireRow.Interior.Color = vbYellow
* * * * Case "Missing": *Target.EntireRow.Interior.Color = vbRed
* * * * Case "Other": * *Target.EntireRow.Interior.Color = vbMagenta
* * End Select
End Sub

HTH,
Wouter

Thanks, that worked perfect, but if I accidently put in Paid Off I
have to manually change the fill is there a way when I delete the
entry, it will put the fill back to original?

On Jan 28, 3:20*pm, Maggie wrote:
On Jan 28, 2:56*pm, RadarEye wrote:





On 28 jan, 20:28, Maggie wrote:

I have a worksheet named Data that I would like for a row to be
highlighted when someone enters Paid Off, Missing and Other . *How do
I do a macro to perform this? *I would like each type to be a
different color *such as Paid off to be highlighted yellow, Missing
red,and Other pink.
Please help

Hai Maggie,

For starters VBA has 8 shorthand colors
vbBlack
vbRed
vbGreen
vbYellow
vbBlue
vbMagenta
vbCyan
vbWhite

Next to this you can use a colorindex.

To come back to your question:
Copy the macro below into the macropar of the sheet where you want the
colorchanging occur

Private Sub Worksheet_Change(ByVal Target As Range)
* * Select Case Target.Value
* * * * Case "Paid off": Target.EntireRow.Interior.Color = vbYellow
* * * * Case "Missing": *Target.EntireRow.Interior.Color = vbRed
* * * * Case "Other": * *Target.EntireRow.Interior.Color = vbMagenta
* * End Select
End Sub

HTH,
Wouter

Thanks, that worked perfect, but if I accidently put in Paid Off I
have to manually change the fill is there a way when I delete the
entry, it will put the fill back to original?- Hide quoted text -

- Show quoted text -

Now it is saying that I need to debug here :
Target.EntireRow.Interior.Color = vbYellow
Help.

On Jan 28, 3:20*pm, Maggie wrote:
On Jan 28, 2:56*pm, RadarEye wrote:





On 28 jan, 20:28, Maggie wrote:

I have a worksheet named Data that I would like for a row to be
highlighted when someone enters Paid Off, Missing and Other . *How do
I do a macro to perform this? *I would like each type to be a
different color *such as Paid off to be highlighted yellow, Missing
red,and Other pink.
Please help

Hai Maggie,

For starters VBA has 8 shorthand colors
vbBlack
vbRed
vbGreen
vbYellow
vbBlue
vbMagenta
vbCyan
vbWhite

Next to this you can use a colorindex.

To come back to your question:
Copy the macro below into the macropar of the sheet where you want the
colorchanging occur

Private Sub Worksheet_Change(ByVal Target As Range)
* * Select Case Target.Value
* * * * Case "Paid off": Target.EntireRow.Interior.Color = vbYellow
* * * * Case "Missing": *Target.EntireRow.Interior.Color = vbRed
* * * * Case "Other": * *Target.EntireRow.Interior.Color = vbMagenta
* * End Select
End Sub

HTH,
Wouter

Thanks, that worked perfect, but if I accidently put in Paid Off I
have to manually change the fill is there a way when I delete the
entry, it will put the fill back to original?- Hide quoted text -

- Show quoted text -

I figured that it causes a debug when I protect the sheet and I need
to protect the sheet because there are formulas.

Try this...

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Value
Case "Paid off"
Target.EntireRow.Interior.Color = vbYellow
Case "Missing"
Target.EntireRow.Interior.Color = vbRed
Case "Other"
Target.EntireRow.Interior.Color = vbMagenta
Case Else
Target.EntireRow.Interior.ColorIndex = xlColorIndexAutomatic
End Select
End Sub

Note that except for the Case Else and the code line under it, this is the
same code as RadarEye posted... I simply removed the "join multiple code
lines" symbol (the colon) and physically split the joined lines into
separate lines (which is my personal preference).

--
Rick (MVP - Excel)


"Maggie" wrote in message
...
On Jan 28, 2:56 pm, RadarEye wrote:
On 28 jan, 20:28, Maggie wrote:

I have a worksheet named Data that I would like for a row to be
highlighted when someone enters Paid Off, Missing and Other . How do
I do a macro to perform this? I would like each type to be a
different color such as Paid off to be highlighted yellow, Missing
red,and Other pink.
Please help

Hai Maggie,

For starters VBA has 8 shorthand colors
vbBlack
vbRed
vbGreen
vbYellow
vbBlue
vbMagenta
vbCyan
vbWhite

Next to this you can use a colorindex.

To come back to your question:
Copy the macro below into the macropar of the sheet where you want the
colorchanging occur

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Value
Case "Paid off": Target.EntireRow.Interior.Color = vbYellow
Case "Missing": Target.EntireRow.Interior.Color = vbRed
Case "Other": Target.EntireRow.Interior.Color = vbMagenta
End Select
End Sub

HTH,
Wouter

Thanks, that worked perfect, but if I accidently put in Paid Off I
have to manually change the fill is there a way when I delete the
entry, it will put the fill back to original?

On Jan 28, 3:40*pm, "Rick Rothstein"
wrote:
Try this...

Private Sub Worksheet_Change(ByVal Target As Range)
* * Select Case Target.Value
* * * * Case "Paid off"
* * * * * * Target.EntireRow.Interior.Color = vbYellow
* * * * Case "Missing"
* * * * * * Target.EntireRow.Interior.Color = vbRed
* * * * Case "Other"
* * * * * * Target.EntireRow.Interior.Color = vbMagenta
* * * * Case Else
* * * * * * Target.EntireRow.Interior.ColorIndex = xlColorIndexAutomatic
* * End Select
End Sub

Note that except for the Case Else and the code line under it, this is the
same code as RadarEye posted... I simply removed the "join multiple code
lines" symbol (the colon) and physically split the joined lines into
separate lines (which is my personal preference).

--
Rick (MVP - Excel)

"Maggie" wrote in message

...
On Jan 28, 2:56 pm, RadarEye wrote:





On 28 jan, 20:28, Maggie wrote:

I have a worksheet named Data that I would like for a row to be
highlighted when someone enters Paid Off, Missing and Other . How do
I do a macro to perform this? I would like each type to be a
different color such as Paid off to be highlighted yellow, Missing
red,and Other pink.
Please help

Hai Maggie,

For starters VBA has 8 shorthand colors
vbBlack
vbRed
vbGreen
vbYellow
vbBlue
vbMagenta
vbCyan
vbWhite

Next to this you can use a colorindex.

To come back to your question:
Copy the macro below into the macropar of the sheet where you want the
colorchanging occur

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Value
Case "Paid off": Target.EntireRow.Interior.Color = vbYellow
Case "Missing": Target.EntireRow.Interior.Color = vbRed
Case "Other": Target.EntireRow.Interior.Color = vbMagenta
End Select
End Sub

HTH,
Wouter

Thanks, that worked perfect, but if I accidently put in Paid Off I
have to manually change the fill is there a way when I delete the
entry, it will put the fill back to original?- Hide quoted text -

- Show quoted text -

Why won't the macro work when I protect the sheet?

On Wed, 28 Jan 2009 13:09:11 -0800 (PST), Maggie
wrote:

Why won't the macro work when I protect the sheet?

Protect in the event.

Private Sub Worksheet_Change(ByVal Target As Range)
Protect Password:="password", userinterfaceonly:=True

rest of code


Gord Dibben MS Excel MVP